Sum Across Sheets based on Grouping Criteria

[detangler]

Here's what I have:

Book2
	A	B	C	D
1	Service Line	Sheet Name	Grouping
2	X-Ray	SL1	Standalone Imaging
3	Computed Tomography	SL2	Imaging Joint Venture
4	Magnetic Resonance Imaging	SL3	Standalone Imaging
5	PET	SL4	East Ambulatory Center
6	Mammography	SL5	East Ambulatory Center
7	Endoscopy	SL6	(Reserved for Future #2)
8	Cystoscopy	SL7	East Ambulatory Center
9	Nuclear Stress	SL8	West Ambulatory Center
10	Cardiac Rehab	SL9	East Ambulatory Center
11	Echocardiology	SL10	Standalone Imaging
12	Occupational Medicine	SL11	Standalone Imaging
13	Radiation Oncology	SL12	West Ambulatory Center
14	Medical Oncology	SL13	East Ambulatory Center
15	Diagnostic Cath	SL14	West Ambulatory Center
16	Interventional Cath	SL15	East Ambulatory Center
17	Orthopedics	SL16	West Ambulatory Center
18	Physical Therapy	SL17	East Ambulatory Center
19	Wellness Center	SL18	(Reserved for Future #1)
20	PET-JV	SL19	(Reserved for Future #1)
21	MRI-JV	SL20	Standalone 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!

 

[detangler]

**bump**

 

[Domenic]

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]

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
	A	B	C	D	E	F
1	Income Statement	2007	2008	2009	2010	2011
2
3	Net Service Revenue	380,000	406,056	288,512	418,240	424,480
4
5	Expenses:
6	Salaries and benefits	3,126,504	2,851,832	3,390,960	2,809,616	3,678,088
7	Pharmaceuticals	1,403,520	1,221,000	1,370,544	1,395,112	1,504,856
8	Office Supplies	856,000	674,864	828,584	826,000	907,304
9	Purchased Services	720,000	539,200	686,160	676,512	750,368
10	Insurance	640,000	459,400	602,408	588,504	657,952
11	Interest	4,160	10,352	13,984	14,312	14,624
12	Management Fees	19,000	20,303	14,426	20,912	21,224
13	Depreciation & Amortization	53,336	58,672	58,672	58,672	58,672
14	Real Estate Occupancy & Rental	-	-	-	-	-
15	Total Operating Expenses	6,822,520	5,835,623	6,965,738	6,389,640	7,593,088
16
17	Operating 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]

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]

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]

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]

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]

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]

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.