Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sumifs with 3d reference to sum across tabs based on three criterias

Posted on 2015-01-06
6
Medium Priority
?
715 Views
Last Modified: 2015-01-06
HI All,

I have a workbook with four sheets. The first sheet is called "Summary" and the other 3 are called (0,1, and 2). The sheets named with a number  basically represents the month.

My summary sheet will contain a formula that the user will choose the month or range of months to sum across the number tabs based on two other criteria like account and Unit number display in the summary tabs

Summary Tab
A1=Month or a range of Months like 1 or range from 0-2

B8=Account
B9=Account
B10=Account

D5=Unit Number

I have tried using indirect with sumifs to no avail.
I think I need a sumproduct,sumifs,indirect combination with range names for the months in D8 of my summary tab.

I'm attaching my file to make it more clear for you.
test.xlsx
0
Comment
Question by:Pachecda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40533961
SUM function can handle them but neither SUMIF nor SUMIFS nor SUMPRODUCT.
 Check this link from MS:
 https://office.microsoft.com/en-us/...range-on-multiple-worksheets-HP010342355.aspx

i can come up with SUMPRODUCT if you want , let me know.
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40534013
check attached file.
EE.xlsx
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40534020
create a named range name it "SHTS" value ={0;1;2}    represents the names of your sheets.

put this formula =SUMPRODUCT(SUMIFS(INDIRECT("'"&SHTS&"'!"&CELL("address",D2)),INDIRECT("'"&SHTS&"'!"&CELL("address",B2)),B8)) in D8 of Summary Sheet then drag down.  see the example attached above
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Pachecda
ID: 40534164
Thanks Professor JimJam,


I like the formula, however, can we expanded to include the Unit number on D5 of the summary tab. I also saw that you created a name SHTS, can we make it dynamic to have the user choose what sheets to sum:

For Instance

On A1, I would like to have a drop down list with the define names as follows:
Bal_0 = {0}
BAl_1 = {0,1}
Bal_2= {0,1,2} same as the shts defined name

Now the user should only select the Bal_0 and the formula should just sum the sheet 0. If the user select Bal_1, the formula should sum sheet 0+1

Thanks for help.
0
 
LVL 27

Accepted Solution

by:
ProfessorJimJam earned 2000 total points
ID: 40534361
here you go, please find attached.
EE.xlsx
0
 

Author Closing Comment

by:Pachecda
ID: 40534516
Awesome Job.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question