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