Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2016-09-07
8
42 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:itjockey
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:itjockey
ID: 41787526
i am in hurry to rush for meeting else i will make it as per your requirement.
1
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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:itjockey
ID: 41787595
See Cell D14 in attached WB
0
 
LVL 8

Expert Comment

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

Thanks
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 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 26

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

789 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