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

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
PachecdaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ProfessorJimJamConnect With a Mentor Commented:
here you go, please find attached.
EE.xlsx
0
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
check attached file.
EE.xlsx
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ProfessorJimJamCommented:
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
 
PachecdaAuthor Commented:
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
 
PachecdaAuthor Commented:
Awesome Job.
0
All Courses

From novice to tech pro — start learning today.