Distinguish Empty cells from '0' value in Sumproduct Boolean?

[Safari Breeze]

Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m trying to distinguish empty cells from ‘0’ values in the (--) boolean 1/0 part of my Sumproduct formula. Actually, I want ‘0’ to be considered a value.
<o></o>
My formula:<o></o>
IF(A1="X",SUMPRODUCT(Range1,Range2)/SUMPRODUCT(--((Range1)>0),Range2)
<o></o>
I tried:<o></o>
…./SUMPRODUCT(--((Range1)>=0),Range2)
But instances of ‘0’ were not counted in the Boolean i.e. FALSE, so results were the same as if cell was empty. (Strange?)<o></o>

Could I use a ISNUMBER function here instead?<o></o>

Or what about coercing the cell to default to 0.01 if a value of 0 is entered? That would be Boolean TRUE.<o></o>

Thanks <o></o>

 

[AlphaFrog]

Try using a SUMIF function instead.

IF(A1="X",SUMPRODUCT(Range1,Range2)/SUMIF(Range1,">=0",Range2)

 

[Safari Breeze]

AlphaFrog...that was awesome!
Thanks a lot and happy jumping to you!