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.


+-------------------------------------------------------------------+
|Filename: DSUM or SUMIF test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3533 |
+-------------------------------------------------------------------+

 

[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("'"&D46&"'!A11:A17"),A4,INDIRECT("'"&D46&"'!B11:B17")))

...where D46 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!

 

[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 #re
error. I had no luck with the other formula you suggested, bu
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?

 

[bruce42]

yes. For Example, I can insert the following formula with accurat
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
large number of sheets are entered. There must be a more concis
method to achieve this result

 

[bruce42]

You were right. After rebuilding the document I can now calculate al
of the sheets in the workbook with no problems. I must have made
typo somewhere in the first version. Is there a way to perform th
same function with worksheets that do not have linear names, such a
"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("'"&D46&"'!A11:A17"),A4,INDIRECT("'"&D46&"'!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, D46 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.