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.