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")))

[Domenic]

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")))

If you have a mix of names, use the first formula I offered...

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

Enter a list of your sheet names in a range of cells and refer to those cells in the formula. In the above formula, D4:D6 contains the sheet names. If you wanted to sum sheets 'Monday' through 'Friday', you could use the following formula...

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT(DATE(2005,6,20)&":"&DATE(2005,6,24))),"dddd")&"'!A11:A17"),A4,INDIRECT("'"&TEXT(ROW(INDIRECT(DATE(2005,6,20)&":"&DATE(2005,6,22))),"dddd")&"'!B11:B17")))

Hope this helps!

[bruce42]

Thank you. That's perfect.

Is there a way to pull data from other files in the same folder as that workbook? All of the other workbooks are formatted the same way.

I'm using the following formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&M2:M43&"'!S10:S54"),G4,INDIRECT("'"&M2:M43&"'!AC10:AC54")))

[Domenic]

Unfortunately, if I'm not mistaken, your other files would need to be opened when using the INDIRECT function. Since you likely would like to avoid having to open relevant files before using the formula, I'd suggest you search the Board/Newsgroups for the 'PULL' function by Harlan Grove. I've never used it, but I believe it will give you what you want without first having to open files.

Thank you. That's perfect.

Is there a way to pull data from other files in the same folder as that workbook? All of the other workbooks are formatted the same way.

I'm using the following formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&M2:M43&"'!S10:S54"),G4,INDIRECT("'"&M2:M43&"'!AC10:AC54")))