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!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
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
19,403

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
19,403
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
19,403
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,458
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,759
Members
412,482
Latest member
arooshrana2
Top