Conditional Array
Posted by Riki Phelan on August 30, 2001 3:14 AM
Hi All,
I have the following array formula returning #VALUE as on of the cells in col G is blank (intentionally):
=SUM(((B4:B2931="0900")*(MONTH(A4:A2931)=1))*(G4:G2931))
It sums col G if:
Col B has the text entry "0900", and
Col A (containing dates dd-mmm-yy) = "1" (for January)
Can anyone suggest how this can be ammended to disregard blank cells?
Tks, Riki
Posted by George on August 30, 2001 3:40 AM
Blank cells in G4:G2931 should not cause any problem.
Are you entering the formula by pressing Ctrl+Shift+Enter?
Posted by Aladin Akyurek on August 30, 2001 3:54 AM
Riki,
I assume that you produce blanks in G by formulas, that is,the cell in the G range are not formula-free.
I'd suggest that you modify those formulas to return 0 instead of blank (""). And, you'd rather prefer not seeing zeroes in column G, you can custom format the range as
[=0]"";General
If you do this, your array formula will work.
You might consider using a *SUMPRODUCT formula in its stead (the above proposal holds also for this type of formula):
=SUMPRODUCT(((B4:B2931="0900")*(MONTH(A4:A2931)=1))*(G4:G2931))
or
=SUMPRODUCT(((B4:B2931="0900")*(MONTH(A4:A2931)=1)),(G4:G2931))
* The SUMPRODUCT formulas are entered normally.
Aladin
=============
Posted by Riki on August 30, 2001 5:16 AM
Hi Aladin, George,
George, yep, entering correctly as CSE.
Actually, I should have stated initially, the blank in col G is the result of a formular.
Aladin, thanks for you sumproduct alternatives. The first always returns an error, but your second works fine, thank you.
By the way, returning a zero in col G is unacceptable as these are dew point temperatures calculated from other data. At times the dew point could be zero degrees.
Anyhow, the second sumproduct has sorted me out - Tar!
Cheers, Riki
