Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2016-09-07
8
Medium Priority
?
57 Views
Last Modified: 2016-09-26
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
Comment
Question by:Danielle Christou
  • 4
  • 2
  • 2
8 Comments
 

Author Comment

by:Danielle Christou
ID: 41787515
This is the file...
Headcount-Planning.xlsx
0
 
LVL 8

Expert Comment

by:Naresh Patel
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

by:Naresh Patel
ID: 41787526
i am in hurry to rush for meeting else i will make it as per your requirement.
1
The 14th Annual Expert Award Winners

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

 

Author Comment

by:Danielle Christou
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

by:Naresh Patel
ID: 41787595
See Cell D14 in attached WB
0
 
LVL 8

Expert Comment

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

Thanks
0
 
LVL 27

Accepted Solution

by:
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

=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
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 41815726
accepted solution is provided already.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

607 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