x
Solved

Posted on 2016-09-07
Medium Priority
57 Views
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
Question by:Danielle Christou
• 4
• 2
• 2

Author Comment

ID: 41787515
This is the file...
0

LVL 8

Expert Comment

ID: 41787523
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

LVL 8

Expert Comment

ID: 41787526
i am in hurry to rush for meeting else i will make it as per your requirement.
1

Author Comment

ID: 41787529
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

LVL 8

Expert Comment

ID: 41787595
See Cell D14 in attached WB
0

LVL 8

Expert Comment

ID: 41787598
Or Wait for few hours ...i will look in to this ....

Thanks
0

LVL 27

Accepted Solution

ProfessorJimJam earned 2000 total points (awarded by participants)
ID: 41787880
@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

EE.xlsm
1

LVL 27

Expert Comment

ID: 41815726
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.