Solved

Sumifs with 3d reference to sum across tabs based on three criterias

Posted on 2015-01-06
6
406 Views
Last Modified: 2015-01-06
HI All,

I have a workbook with four sheets. The first sheet is called "Summary" and the other 3 are called (0,1, and 2). The sheets named with a number  basically represents the month.

My summary sheet will contain a formula that the user will choose the month or range of months to sum across the number tabs based on two other criteria like account and Unit number display in the summary tabs

Summary Tab
A1=Month or a range of Months like 1 or range from 0-2

B8=Account
B9=Account
B10=Account

D5=Unit Number

I have tried using indirect with sumifs to no avail.
I think I need a sumproduct,sumifs,indirect combination with range names for the months in D8 of my summary tab.

I'm attaching my file to make it more clear for you.
test.xlsx
0
Comment
Question by:Pachecda
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40533961
SUM function can handle them but neither SUMIF nor SUMIFS nor SUMPRODUCT.
 Check this link from MS:
 https://office.microsoft.com/en-us/...range-on-multiple-worksheets-HP010342355.aspx

i can come up with SUMPRODUCT if you want , let me know.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40534013
check attached file.
EE.xlsx
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40534020
create a named range name it "SHTS" value ={0;1;2}    represents the names of your sheets.

put this formula =SUMPRODUCT(SUMIFS(INDIRECT("'"&SHTS&"'!"&CELL("address",D2)),INDIRECT("'"&SHTS&"'!"&CELL("address",B2)),B8)) in D8 of Summary Sheet then drag down.  see the example attached above
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Pachecda
ID: 40534164
Thanks Professor JimJam,


I like the formula, however, can we expanded to include the Unit number on D5 of the summary tab. I also saw that you created a name SHTS, can we make it dynamic to have the user choose what sheets to sum:

For Instance

On A1, I would like to have a drop down list with the define names as follows:
Bal_0 = {0}
BAl_1 = {0,1}
Bal_2= {0,1,2} same as the shts defined name

Now the user should only select the Bal_0 and the formula should just sum the sheet 0. If the user select Bal_1, the formula should sum sheet 0+1

Thanks for help.
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40534361
here you go, please find attached.
EE.xlsx
0
 

Author Closing Comment

by:Pachecda
ID: 40534516
Awesome Job.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

790 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