=Countif (not zero)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 19 Jun 2005 21:06 GMT

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

Reply to this Message

Ragdyer - 19 Jun 2005 21:21 GMT

Try these:

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

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

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Hello All,
> Using Excel XP.
[quoted text clipped - 18 lines]
>
> Mike

Reply to this Message

JE McGimpsey - 19 Jun 2005 21:21 GMT

one way:

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

> Hello All,
> Using Excel XP.
[quoted text clipped - 18 lines]
>
> Mike

Reply to this Message

Mike - 19 Jun 2005 21:33 GMT

thanks for your help, got it working.
Mike

> one way:
>
[quoted text clipped - 23 lines]
>>
>> Mike

Reply to this Message

Bill Kuunders - 19 Jun 2005 21:32 GMT

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