Trouble with formula 4

[Maisie2007]

I cannot figure this one out:

What I want to do is make K20 display nothing in it’s cell if I25, or I26, or I27 (and all the way down to cell I39) has a number greater than zero.
If not, I want it to total H4:H18, which the answer should be 5

Right now, it doesn’t matter if I have nothing in I25, or if I have 1, or 2, or 3, etc – K20 displays nothing.

Here's the formula I'm using in K20: =IF(OR(I25>=0,I26>=0,I27>=0,I28>=0,I29>=0,I30>=0,I31>=0,I32>=0,I33>=0,I34>=0,I35>=0,I36>=0,I37>=0,I38>=0,I39>=0),"",SUM(H4:H18))

What am I doing wrong?

Thanks!

 

[lczlcz]

well, your formula really looks clumsy.
Why don't you try this in cell K25?

If(sum(I25:I39)>0,0,sum(H4:H18))

 

[lczlcz]

oops, a typo here, should be cell K20 instead of K25.

 

[yogia]

Hi Maisie2007:

How about ...

=IF(SUM(I25:I39),"",SUM(H4:H18))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[macropod]

Hi Maisie2007,

Right now, it doesn’t matter if I have nothing in I25, or if I have 1, or 2, or 3, etc – K20 displays nothing.

According to your formula, that's what you should get. The only time your formula will return a value from sum(H4:H18) is if the values in all the referenced cells in Column I have negative values.

Neither will the formulae suggested by lczlcz and yogia work correctly, since they can both return false positives and false negatives.

If you want the formula to hide the sum if all of the referenced cells in Column I are greater than 0, then all you need to do is to change your 'OR' to 'AND'.

Cheers

[MS MVP - Word]

 

[combo]

You can use COUNTIF:
=IF(COUNTIF(I25:I39,">=0")>0,"",SUM(H4:H18))

combo

 

[Maisie2007]

This one seems to work just fine: =IF(COUNTIF(I25:I39,">=0")>0,"",SUM(H4:H18))

Thanks so much for all your answers, it's much appreciated!