Sum of Categories by Record across Multiple Worksheets

[bruce42]

In the following formula, A11:A17 contains names of various clients, of which there are hundreds. A4 refers to a specific cell on the first sheet of the workbook into which I could type a specific client name as needed. A11:A17 is varying quantities. I want to be able to type the client name into cell A4 and have the formula then calculate the sum of A11:A17 throughout the entire workbook.

=SUMIF(A11:A17,A4,B11:B17)+SUMIF(Sheet2!A11:A17,A4,Sheet2!B11:B17)+SUMIF(Sheet3!A11:A17,A4,Sheet3!B11:B17)

The above formula works fine for a limited number of worksheets. The workbook I'm trying to work with is over 40 sheets, however. I've tried both DSUM and SUMIF with limited results. They both resist 3-D references. Insert>Name>Define doesn't seem to help either. I have to asume that there is a more elegant way to achieve this calculation, but my knowledge of Excel is execrable. Your help is greatly appreciated.

I have attached a zipped Excel file that I'm using as a test.

[Domenic]

Try...

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A11:A17"),A4,INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!B11:B17")))

OR

=SUMPRODUCT(SUMIF(INDIRECT("'"&D4:D6&"'!A11:A17"),A4,INDIRECT("'"&D4:D6&"'!B11:B17")))

...where D4:D6 contains the sheet names. If, for example, you have 40 sheets, and
your actual sheets are named Sheet1, Sheet2, etc., use the first formula and change ROW(INDIRECT("1:3")) to ROW(INDIRECT("1:40")).

Hope this helps!

In the following formula, A11:A17 contains names of various clients, of which there are hundreds. A4 refers to a specific cell on the first sheet of the workbook into which I could type a specific client name as needed. A11:A17 is varying quantities. I want to be able to type the client name into cell A4 and have the formula then calculate the sum of A11:A17 throughout the entire workbook.

=SUMIF(A11:A17,A4,B11:B17)+SUMIF(Sheet2!A11:A17,A4,Sheet2!B11:B17)+SUMIF(Sheet3!A11:A17,A4,Sheet3!B11:B17)

The above formula works fine for a limited number of worksheets. The workbook I'm trying to work with is over 40 sheets, however. I've tried both DSUM and SUMIF with limited results. They both resist 3-D references. Insert>Name>Define doesn't seem to help either. I have to asume that there is a more elegant way to achieve this calculation, but my knowledge of Excel is execrable. Your help is greatly appreciated.

I have attached a zipped Excel file that I'm using as a test.

[bruce42]

I tried the following with limited results:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!A11:A17"),A4,INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B11:B17")))

It works for up to 12 worksheets, but after that it returns a #ref error. I had no luck with the other formula you suggested, but perhaps I'm not getting the syntax correct for the sheet names. Thanks.

[Domenic]

Are the rest of the sheets named the same way, Sheet13, Sheet14, Sheet15, etc?

I tried the following with limited results:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!A11:A17"),A4,INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B11:B17")))

It works for up to 12 worksheets, but after that it returns a #ref error. I had no luck with the other formula you suggested, but perhaps I'm not getting the syntax correct for the sheet names. Thanks.

[bruce42]

yes. For Example, I can insert the following formula with accurate results:

=SUMIF(DBType,A4,DBAmount)+SUMIF(Sheet2!A11:A17,Sheet1!A4,Sheet2!B11:B17)+SUMIF(Sheet3!A11:A17,Sheet1!A4,Sheet3!B11:B17)+SUMIF(Sheet4!A11:A17,Sheet1!A4,Sheet4!B11:B17)+SUMIF(Sheet5!A11:A17,Sheet1!A4,Sheet5!B11:B17)+SUMIF(Sheet6!A11:A17,Sheet1!A4,Sheet6!B11:B17)+SUMIF(Sheet7!A11:A17,Sheet1!A4,Sheet7!B11:B17)+SUMIF(Sheet8!A11:A17,Sheet1!A4,Sheet8!B11:B17)+SUMIF(Sheet9!A11:A17,Sheet1!A4,Sheet9!B11:B17)+SUMIF(Sheet10!A11:A17,Sheet1!A4,Sheet10!B11:B17)+SUMIF(Sheet11!A11:A17,Sheet1!A4,Sheet11!B11:B17)+SUMIF(Sheet12!A11:A17,Sheet1!A4,Sheet12!B11:B17)+SUMIF(Sheet13!A11:A17,Sheet1!A4,Sheet13!B11:B17)+SUMIF(Sheet14!A11:A17,Sheet1!A4,Sheet14!B11:B17)+SUMIF(Sheet15!A11:A17,Sheet1!A4,Sheet15!B11:B17)+SUMIF(Sheet16!A11:A17,Sheet1!A4,Sheet16!B11:B17)+SUMIF(Sheet17!A11:A17,Sheet1!A4,Sheet17!B11:B17)+SUMIF(Sheet18!A11:A17,Sheet1!A4,Sheet18!B11:B17)+SUMIF(Sheet19!A11:A17,Sheet1!A4,Sheet19!B11:B17)+SUMIF(Sheet20!A11:A17,Sheet1!A4,Sheet20!B11:B17)+SUMIF(Sheet21!A11:A17,Sheet1!A4,Sheet21!B11:B17)

This is a very clumsy solution, and may start to break down after a large number of sheets are entered. There must be a more concise method to achieve this result.

[bruce42]

You were right. After rebuilding the document I can now calculate all of the sheets in the workbook with no problems. I must have made a typo somewhere in the first version. Is there a way to perform the same function with worksheets that do not have linear names, such as "Status, Monday, Tuesday, etc.?

The current version of the formula is:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:16"))&"'!A11:A17"),A4,INDIRECT("'Sheet"&ROW(INDIRECT("1:16"))&"'!B11:B17")))