If Statement / Array

[JEFF]

Hello,

I have data in columns A, B, and C. In column D, I would like a formula
that sums the contents of A1:C1, but only if none of the cells contains a
zero. In other words, if all the cells are greater than zero, return the
sum. If one of the cells has a figure of zero, return zero.

Help?! TIA

 

[T. Valko]

One way:

=(COUNTIF(A1:C1,0)=0)*SUM(A1:C1)

Empty cells are ignored.

 

[Tom Hutchins]

Try this in D1:

=IF(OR(A1=0,B1=0,C1=0),0,SUM(A1:C1))

Hope this helps,

Hutch

 

[Mike H]

Try,

=SUM(IF(MIN(A1:C1)>0,A1:C1))

Mike

 

[JEFF]

It does, thanks.... Was wondering if I had 100 columns instead of three,
what my approach would be.....

Thanks again

 

[carrol]

what if i have a formula, lets say 1+a , whereby i need to get the sum of
this "1+a" for a = 1 to 20??

 

[T. Valko]

Try this:

=SUMPRODUCT(1+ROW(INDIRECT("1:20")))