# Thread: IF AND OR help (Excel 2002)

1. ## IF AND OR help (Excel 2002)

I need to have a way to flag if two or more cells out of five have values greater than 0. Assume the data is in A1, B1, C1, D1, E1. What logical formula can I put in F1 so that something is returned in F1 if at least two of the cells have values greater than 0? I know there must be a way to do this with IFs ANDs or ORs but I haven't been able to figure it out.

2. ## Re: IF AND OR help (Excel 2002)

<P ID="edit" class=small>(Edited by Rudi on 02-Feb-08 09:30. Added array function!)</P>There is probably an array based formula that can shorten this expression, but for now, this formula works:
=IF(IF(A1>0,1,)+IF(B1>0,1,0)+IF(C1>0,1,0)+IF(D1>0, 1,0)+IF(E1>0,1,0)>1,"Two or more cells are greater than zero","Passed")

I managed to work out an array formula to do this too...Its a bit shorter!
=IF(SUM(IF(A2:E2>0,1))>1,"Two or more cells are greater than zero","Passed")
Please remember to confirm entry of this formula by pressing CTRL+SHIFT+ENTER!

3. ## Re: IF AND OR help (Excel 2002)

Wouldn't that be

=IF(SUM(IF(A1:E1>0,1))>1,"Two or more cells are greater than zero","Passed")

4. ## Re: IF AND OR help (Excel 2002)

You can also use this formula; it is not an array formula, you don't have to use Ctrl+Shift+Enter to confirm it:
<code>
=IF(COUNTIF(A1:E1,">0")>1,"At least two positive","")
</code>
This formula only returns a text in F1 if at least two values are positive.

5. ## Re: IF AND OR help (Excel 2002)

I was testing the formula in row two to see if it gave the same results as the "long" one, and forgot to change the references back to row one.

6. ## Re: IF AND OR help (Excel 2002)

Thanks to both of you for the solutions. This is exactly what I needed!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•