[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2015-01-06
6
Medium Priority
?
801 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
  • 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
Technology Partners: 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

831 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