Sum Across Sheets based on Grouping Criteria

detangler

Board Regular
Joined
Oct 21, 2003
Messages
74
Here's what I have:
Book2
ABCD
1Service LineSheet NameGrouping
2X-RaySL1Standalone Imaging
3Computed TomographySL2Imaging Joint Venture
4Magnetic Resonance ImagingSL3Standalone Imaging
5PETSL4East Ambulatory Center
6MammographySL5East Ambulatory Center
7EndoscopySL6(Reserved for Future #2)
8CystoscopySL7East Ambulatory Center
9Nuclear StressSL8West Ambulatory Center
10Cardiac RehabSL9East Ambulatory Center
11EchocardiologySL10Standalone Imaging
12Occupational MedicineSL11Standalone Imaging
13Radiation OncologySL12West Ambulatory Center
14Medical OncologySL13East Ambulatory Center
15Diagnostic CathSL14West Ambulatory Center
16Interventional CathSL15East Ambulatory Center
17OrthopedicsSL16West Ambulatory Center
18Physical TherapySL17East Ambulatory Center
19Wellness CenterSL18(Reserved for Future #1)
20PET-JVSL19(Reserved for Future #1)
21MRI-JVSL20Standalone Imaging
Sheet1


Sheet Name is the name to all of the worksheets that I need to sum. All of these sheets (income statements) are formatted the exact same way. Each of these statements need to roll up to its specified grouping. So the end product is that I will have 6 rollup summary sheets formatted the exact same way as these individual income statement.

I have read and re-read the Index, Match, Indirect, SumIf functions and posts on this site over and over again and just couldn't figure it out. Can someone please help!!! Thank you so much!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,216
Office Version
  1. 365
Platform
  1. Windows
I'm not sure what it is you're looking for. Can you elaborate? It would help if you could provide a sample of an income statement and a sample of a summary sheet?
 

detangler

Board Regular
Joined
Oct 21, 2003
Messages
74
I'm sorry that I have not been clear...

Here's a sample of each income statement (they are just your standard, everyday generic income statement):
Book1
ABCDEF
1Income Statement20072008200920102011
2
3Net Service Revenue380,000406,056288,512418,240424,480
4
5Expenses:
6Salaries and benefits3,126,5042,851,8323,390,9602,809,6163,678,088
7Pharmaceuticals1,403,5201,221,0001,370,5441,395,1121,504,856
8Office Supplies856,000674,864828,584826,000907,304
9Purchased Services720,000539,200686,160676,512750,368
10Insurance640,000459,400602,408588,504657,952
11Interest4,16010,35213,98414,31214,624
12Management Fees19,00020,30314,42620,91221,224
13Depreciation & Amortization53,33658,67258,67258,67258,672
14Real Estate Occupancy & Rental-----
15Total Operating Expenses6,822,5205,835,6236,965,7386,389,6407,593,088
16
17Operating Income (Loss)(6,442,520)(5,429,567)(6,677,226)(5,971,400)(7,168,608)
Sheet1


I've got 20 of the above worksheets that are named "SL1", "SL2"... all the way to "SL20" respectively.

Then, according to the Input worksheet (see my original post), each of these statements are grouped according to 6 categories. So the next step is to sum up these 20 statments by their groupings. For example, the first rollup summary sheet will be for Standalone Imaging and it should sum up all of the numbers from SL1, SL3, SL10, SL11, and SL20 in the exact same format that these income statements are in.

Of course, the whole point of this exercise is just so that a user can always change the way the income statements are grouped... so the grouping will need to stay dynamic. But I have a hard time figuring out an elegant way to roll these income statements up. Your help is greatly appreciated!!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Assuming that Sheet1 contains your input, enter the following formula in B3 of your Standalone Imaging summary sheet, and copy to all other relevant cells...

=SUM(IF(Sheet1!$C$1:$C$20="Standalone Imaging",N(INDIRECT("SL"&MID(Sheet1!$B$1:$B$20,3,1024)+0&"!"&CELL("address",B3)))))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Repeat this process for your other summary sheets and their respective groups. Note that you will find it somewhat slow. However, if it's too slow for your liking, you can always set the 'Calculation' mode to manual.

Hope this helps!
 

detangler

Board Regular
Joined
Oct 21, 2003
Messages
74
Wow... thank you so much!!! It works like magic! I really do have to learn more about CSE formulas.

Just for my education, can you tell me why the N function is used?
 

detangler

Board Regular
Joined
Oct 21, 2003
Messages
74

ADVERTISEMENT

Wow... thank you so much!!! It works like magic! I really do have to learn more about CSE formulas.

Just for my education, can you tell me why the N function is used?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,216
Office Version
  1. 365
Platform
  1. Windows
detangler said:
Wow... thank you so much!!!...

You're very welcome! Glad I could help!

Just for my education, can you tell me why the N function is used?

Sure! The INDIRECT part of the formula returns an array of references. The N function is used for 'de-referencing'. Basically, it makes the values from the array of references available.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,216
Office Version
  1. 365
Platform
  1. Windows
Actually, after taking another look at this, the following formula would suffice...

=SUM(IF(Sheet1!$C$1:$C$20="Standalone Imaging",N(INDIRECT(Sheet1!$B$1:$B$20&"!"&CELL("address",B3)))))

...confirmed with CONTROL+SHIFT+ENTER.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
I not sure if this is any faster or correct,

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C1:C10"),A2,INDIRECT("'"&SheetList&"'!B1:B10")))

Where SheetList is a named ranged with your sheet names, C1:C10 the criteria range, A2 the criteria and B1:B10 the range to sum. Enter with just Enter.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,805
Messages
5,833,766
Members
430,230
Latest member
Yimmie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top