=Countif (not zero)

[Mike]

Hello All,
Using Excel XP.

I have the following sample worksheet:
A
--------------------
1 17
2 6
3 20
4 0
5 11
6 0
-----------------
7 2 (=countif(A1:A6,"<15", ???

I want to count the values in A1:A6 if they are less than 15 but not
counting any zero's.
In A7 I would want the result to be 2. Any help writing the formula would
be
appreciated.

Mike

 

[Ragdyer]

Try these:

=COUNTIF(A1:A6,"<15")-COUNTIF(A1:A6,0)

=SUMPRODUCT((A1:A6>0)*(A1:A6<15))

 

[JE McGimpsey]

one way:

=SUMPRODUCT(--(A1:A6<15),--(A1:A6<>0))

 

[Bill Kuunders]

=COUNTIF(A1:A6,"<15")-COUNTIF(A1:A6,"=0")
did the trick for me

 

[Mike]

thanks for your help, got it working.
Mike

one way:

=SUMPRODUCT(--(A1:A6<15),--(A1:A6<>0))

 

[Lewis Clark]

Here are two ways:

=COUNTIF(A1:A6,"<15") - COUNTIF(A1:A6, "=0")


=SUMPRODUCT(--(A1:A6>0),--(A1:A6<15) )


Hope this helps.