?
Solved

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

Posted on 2015-01-06
6
Medium Priority
?
605 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
[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
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
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: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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

764 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