On 2002-09-25 10:14, em wrote:
Aladin - you are correct.
I have one sheet (sheet 1) that we do all manual inputs to columns A thru J.
Those cells are linked to sheet 2 columns A thru J.
Then sheet 3 is the summary sheet. The summary sheet (sheet 3) uses 2 columns linked from sheet 2.
The funny thing is that the formula below works--the only difference in it and the one that doesn't work is that it only sums 1 column instead of 3?
So even though the first statements in the formula are using formualated/linked cells it works--its just the that multiple summing of columns that meet the criteria are not working in the other formula?
=SUMPRODUCT((LEFT('test2'!$B$9:$B$108,2)="3Y")*('test2'!$E$9:$E$108="test"),'test2'!$AD$9:$AD$108)
There is nothing here of a surprise.
The comma that separates the range to sum from the condition tests takes care of the text values in the range to sum. Change it * and you'll get a #VALUE! error because you will be multiplying a text value with a 1 or a 0. The longer formula is trying to add (or to OR) a number with a text value, hence the error value.
Change the linking formulas according the following logic:
=IF(Sheet1!A1,Sheet1!A1,0)
Thus a 0, not a "".
Select such cells and custom format them as:
[=0]"";General
The longer formula Ian suggested will work as intended.