If Statement / Array
 

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

If Statement / Array
 

One way:

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

Empty cells are ignored.

--
Biff
Microsoft Excel MVP


"JEFF" wrote in message
...
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

If Statement / Array
 

Try this in D1:

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

Hope this helps,

Hutch

"JEFF" wrote:

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

If Statement / Array
 

Try,

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

Mike

"JEFF" wrote:

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

If Statement / Array
 

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

Thanks again

"Tom Hutchins" wrote:

Try this in D1:

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

Hope this helps,

Hutch

"JEFF" wrote:

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

If Statement / Array
 

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

If Statement / Array
 

Try this:

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

--
Biff
Microsoft Excel MVP


"carrol" wrote in message
...
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??