Excel sum function that returns zero, if any value in the sum range contains zero

Question

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.

Answer 1

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))

Answer 2

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,

Answer 3

=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