Solved

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

Posted on 2016-09-07
8
30 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
Comment Utility
This is the file...
Headcount-Planning.xlsx
0
 
LVL 8

Expert Comment

by:itjockey
Comment Utility
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:itjockey
Comment Utility
i am in hurry to rush for meeting else i will make it as per your requirement.
1
 

Author Comment

by:Danielle Christou
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:itjockey
Comment Utility
See Cell D14 in attached WB
0
 
LVL 8

Expert Comment

by:itjockey
Comment Utility
Or Wait for few hours ...i will look in to this ....

Thanks
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points (awarded by participants)
Comment Utility
@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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
accepted solution is provided already.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now