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