Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-07
8
Medium Priority
?
51 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
[X]
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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

604 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