Count cells that meet a specific criteria

  • Thread starter Thread starter Wellie
  • Start date Start date
W

Wellie

I have a range of cells that contains a value ranges from
0 to N. For example range A1:F1.
A B C D E F
1 3 0 2 12 0 8
I'm interested in counting number of cells that are
1 - >0 and place the count in one cell (B1)
2 - >5 & <0 and place the count in another cell (C1)

I tried the following and it did not work.

Can someone please help me what kind of worksheet
function that I need to get the right count ?

In cell B1=COUNT(AND(A1<>0,B1<>0,C1<>0,D1<>0,E1<>0,F1<>0))
The result of above should be 4 in B1

Don't know how to do #2

Can someone please help ?
 
One way:

(You can't put the formulae in the same cells as you're counting without
causing a circular reference).

G1: =COUNTIF(A1:F1,">0")
H1: =COUNTIF(A1:F1,">5") + COUNTIF(A1:F1,"<0")
 
I found out how to do the first one which is
countif(A1:F1,">0").

Can someone please help for the 2nd one ?
i.e. countif only it is >5 & <0.

Thanks in advance.
countif(A1:F1,and("a
 
a number can't be both >5 and <0.

If you mean greater than 0 and less than 5:

One way:

=COUNTIF(A1:F1,">0") - COUNTIF(A1:F1>=5)

another:

=SUMPRODUCT(--(A1:F1>0),--(A1:F1<5))
 
Back
Top