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!B1 1: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. InsertNameDefine 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 |
+-------------------------------------------------------------------+

--
bruce42
------------------------------------------------------------------------
bruce42's Profile: http://www.excelforum.com/member.php...o&userid=24609
View this thread: http://www.excelforum.com/showthread...hreadid=381963

Try...

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:3"))&"'!A11:A17"),A4,INDIRECT("'Sheet"&ROW(INDIR ECT("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!

bruce42 Wrote:
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!B1 1: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. InsertNameDefine 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
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=381963

I tried the following with limited results:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:12"))&"'!A11:A17"),A4,INDIRECT("'Sheet"&ROW(INDI RECT("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
------------------------------------------------------------------------
bruce42's Profile: http://www.excelforum.com/member.php...o&userid=24609
View this thread: http://www.excelforum.com/showthread...hreadid=381963

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


bruce42 Wrote:
I tried the following with limited results:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:12"))&"'!A11:A17"),A4,INDIRECT("'Sheet"&ROW(INDI RECT("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
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=381963

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

=SUMIF(DBType,A4,DBAmount)+SUMIF(Sheet2!A11:A17,Sh eet1!A4,Sheet2!B11:B17)+SUMIF(Sheet3!A11:A17,Sheet 1!A4,Sheet3!B11:B17)+SUMIF(Sheet4!A11:A17,Sheet1!A 4,Sheet4!B11:B17)+SUMIF(Sheet5!A11:A17,Sheet1!A4,S heet5!B11:B17)+SUMIF(Sheet6!A11:A17,Sheet1!A4,Shee t6!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:B1 7)+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)+S UMIF(Sheet14!A11:A17,Sheet1!A4,Sheet14!B11:B17)+SU MIF(Sheet15!A11:A17,Sheet1!A4,Sheet15!B11:B17)+SUM IF(Sheet16!A11:A17,Sheet1!A4,Sheet16!B11:B17)+SUMI F(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(S heet20!A11:A17,Sheet1!A4,Sheet20!B11:B17)+SUMIF(Sh eet21!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
------------------------------------------------------------------------
bruce42's Profile: http://www.excelforum.com/member.php...o&userid=24609
View this thread: http://www.excelforum.com/showthread...hreadid=381963

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(INDI RECT("1:16"))&"'!B11:B17")))


--
bruce42
------------------------------------------------------------------------
bruce42's Profile: http://www.excelforum.com/member.php...o&userid=24609
View this thread: http://www.excelforum.com/showthread...hreadid=381963

bruce42 Wrote:
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(INDI RECT("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(D ATE(2005,6,20)&":"&DATE(2005,6,24))),"dddd")&"'!A1 1:A17"),A4,INDIRECT("'"&TEXT(ROW(INDIRECT(DATE(200 5,6,20)&":"&DATE(2005,6,22))),"dddd")&"'!B11:B17") ))

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=381963

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


--
bruce42
------------------------------------------------------------------------
bruce42's Profile: http://www.excelforum.com/member.php...o&userid=24609
View this thread: http://www.excelforum.com/showthread...hreadid=381963

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.

bruce42 Wrote:
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
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=381963