• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

3D Sumifs - Cannot get them to work - Please help!!!

Hi,

I am trying to get a 3D sumifs formula to work...

I have multiple client tabs (currently over 50 and this can constantly grow and shrink) all have the exact same format and layout.

I am trying to create a calculations tab that i can then run some pivots from, to do this i have looked in here and found some 3D sumifs but i cant get it to work? i am getting the #NAME? error

This is my formula in my calculations tab...
=SUMPRODUCT(SUMIFS(INDIRECT("'*"&'Worksheet Names'!$A$1:$A$22&"*'!$B$14:$B$33"),INDIRECT("'*"&'Worksheet Names'!$A$1:$A$22&"*'!$A$14:$A$33"),A2,INDIRECT("'*"&'Worksheet Names'!$A$1:$A$22&"*'!$A$12"),$B$2))

In my client tabs I have a set table (A14:M33), Column A is the Role Type (which has to match cell A2 in the calculations tab), in Cell A12 is a location for the client sheet (which has to match cell B2 in the calculations tab), Column B is the numbers i want to sum if the criteria matches.

Sorry but i cant work out how to upload my document for you to see.
0
Danielle Christou
Asked:
Danielle Christou
  • 4
  • 2
  • 2
1 Solution
 
Danielle ChristouAuthor Commented:
This is the file...
Headcount-Planning.xlsx
0
 
Naresh PatelTraderCommented:
Try Attached Sheet ...i guess this will help you out ...i found Code in EE it self ...it is created by Sir.Byundt.

thanks
3D-Functions---Sum-For-All-Sheet.xlsm
1
 
Naresh PatelTraderCommented:
i am in hurry to rush for meeting else i will make it as per your requirement.
1
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Danielle ChristouAuthor Commented:
Thanks itjockey, but as far as i can see this only shows the SUMIF function that i have got to work, its the SUMIFS that i cant get to work?
0
 
Naresh PatelTraderCommented:
See Cell D14 in attached WB
0
 
Naresh PatelTraderCommented:
Or Wait for few hours ...i will look in to this ....

Thanks
0
 
ProfessorJimJamCommented:
@Danielle Christou

your original formula was a mess :-)  . so, i built up from the scratch.

please see attached file and it is tested and 100% works.

i set up dynamic range, so if you add more sheets with similar structure it is added in the formula automatically.

i put the formula is C2 and drag down and right

=SUMPRODUCT(SUMIFS(INDIRECT("'"&DynamicRangeSheets&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1","")&"$14:"&SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1","")&"$33"),INDIRECT("'"&DynamicRangeSheets&"'!"&"$A$14:$A$33"),Calculations!$A2)*(T(INDIRECT("'"&DynamicRangeSheets&"'!"&"$A$12"))=Calculations!$B2))

Open in new window

EE.xlsm
1
 
ProfessorJimJamCommented:
accepted solution is provided already.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now