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