COUNTIF with OR in formula

[ccepaulb]

I have a problem here.
I want to count the number of cells that meet a certain criteria. The problem is that the cells are not all immediately adjacent to each other, for example let's say I have a value in column e4,h4,i4,l4,o4 & r4. I want the formula to count how many of these selected cells have a value greater than 0.
I was trying to do something to the tune of =countif(or(e4,h4,i4,o4,r4)>0). But it did not work. I know I'm way off on this, can anyone help?

Thanks, Paul

 

[mbarron]

This should get you what you're looking for.
=SUM(E4>0,H4>0,I4>0,L4>0,O4>0,R4>0)

Mike

 

[ccepaulb]

I see where you are going here, but I do not want to sum the values in the cells, I want to sum the number of times a cell has a value greater than 0

Thanks, Paul

 

[cLFlaVA]

Nah, mbarron is 100% correct.

E4>0 returns true if E4 is greater than 0. True is equivalent to 1.

This will take the sum of the number of times True is returned in that statement.

Think of it like this:

=SUM(1,0,0,1,0,1,0...)


*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[mbarron]

Did you try the formula?

If there is a value greater that zero, the true is evaluated as one a false is evaluated as zero.


Mike

 

[ccepaulb]

It did not work, it simply summed up the values???

 

[mbarron]

All of the values or only the ones greater than zero?

Did you use the formula exactly as I posted?

Mike

 

[ccepaulb]

I did it exactly, it only summed up the values greatly than zero

 

[cLFlaVA]

I tried it, it worked on my machine.

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[mbarron]

The only way I could get it to add the values that were greater than zero accurately was if the values were 1.

I'm at a loss.

Mike

 

[cLFlaVA]

Huh?

I put this in A1:

=SUM(A2>0,A3>0,A4>0,A5>0,A6>0)

Then, in A2, A3, A4, A5 and A6, respectively, I put this:

-12,15,0,1,4

And A1 had the value of 3.

3 values greater than 0:
15, 1, and 4.


*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[ccepaulb]

I feel like an idiot, it does work for me, I have no idea what happened the first time I tried it

Thank you for your help!!!