I would like Excel to return answer as zero if any of the values in that sum range is zero.
I have tried a combination of logical tests, but I can't get what I want.
My immediate reaction was also multiplication but perhaps less computationally intensive would be to sum only if a zero is not found in the source.
Assuming the array is A1:C3
:
=IF(COUNTIF(A1:C3,"=0")>0,0,SUM(A1:C3))
You could multiply all the values in the range. If one value is zero the result will be zero and then you can use that in a test to either multiply the sum by 0 or 1.
Cheers,
=MAX(0,MIN(RANGE))
will return the minimal positive integer value in the range if no zero is present, otherwise zero. change MIN to MAX to return the maximal positive integer value