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!

 

[Domenic]

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.

I don't think that will work. But if the OP is willing to change the 'Input' sheet, it could be made faster. So, in case the OP is interested, here's how it would work...

1) Set-up the 'Input' sheet so that the first row contains the group heading, and their corresponding sheet names are listed below their respective group...

Standalone Imaging | Imaging Joint Venture
SL1 | SL2
SL3 | 

...and so on

2) Define named ranges for each group. For example, you could name A2:A10, 'Standalone_Imaging', name B2:B10, 'Imaging_Joint_Venture', and so on. Note that these named ranges would be made dynamic so that sheet names can easily be added or deleted.

3) Enter the following formula in B3 and copy to other relevant cells.

=SUMPRODUCT(N(INDIRECT(Standalone_Imaging&"!"&CELL("address",B3))))

 

[detangler]

Thank you both for the subsequent insights. I'll try them out. I'm very likely to have more questions later. Thanks!!

 

[detangler]

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

I am able to use Brian's formula to get where I need to go and now I have a couple of follow-up questions regarding the logic:

1. I'm still not quite sure how the N function works. I looked it up in Excel Help but the explanation seemed straightforward enough but didn't mention anything about "de-referencing". I also looked up the INDIRECT function and it mentioned nothing about the need to "de-reference".

I also tried taking out the N function from the formula Brian provided and it seemed to return only the first element of the array back.

I wonder if someone can find a different way to explain how this work to me.

2. I'm guessing that the reason for using the CELL function in the formula is just so that the formula can be copied down and across without needing to be changed manually.

However, it has been a nightmare whenever rows are added or deleted in the subsequent sheets (SL.....). Each time that happens, I have to go to the Summary sheet to change the cell reference manually. Is there a better way?

 

[Domenic]

1. I'm still not quite sure how the N function works. I looked it up in Excel Help but the explanation seemed straightforward enough but didn't mention anything about "de-referencing". I also looked up the INDIRECT function and it mentioned nothing about the need to "de-reference".

I also tried taking out the N function from the formula Brian provided and it seemed to return only the first element of the array back.

I wonder if someone can find a different way to explain how this work to me.

Try searching the Board using a keyword such as 'de-referencing' and see if that helps...

2. I'm guessing that the reason for using the CELL function in the formula is just so that the formula can be copied down and across without needing to be changed manually.

However, it has been a nightmare whenever rows are added or deleted in the subsequent sheets (SL.....). Each time that happens, I have to go to the Summary sheet to change the cell reference manually. Is there a better way?

Assuming that any change is made uniformly across all relevant sheets, try the following formula instead...

=SUM(IF(Sheet1!$C$1:$C$20="Standalone Imaging",N(INDIRECT(Sheet1!$B$1:$B$20&"!"&ADDRESS(ROW(SL1!B3),COLUMN(SL1!B3))))))

Hope this helps!

 

[detangler]

I'm sorry... I just realize that it's not Brian's formula that I have used... it's Domenic's. Thank you for the additional explanation as well, Domenic. I will do some more digging and try out the new formula. I will post back later.