# =Countif (not zero)

1. ## =Countif (not zero)

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

2. ## Re: =Countif (not zero)

Try these:

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

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike" <windme@cox.net> wrote in message
> 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
>
>
>
>

3. ## Re: =Countif (not zero)

one way:

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

wrote:

> 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

4. ## Re: =Countif (not zero)

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

--
Greetings from New Zealand
Bill K

"Mike" <windme@cox.net> wrote in message
> 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
>
>
>
>

5. ## Re: =Countif (not zero)

thanks for your help, got it working.
Mike
"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-BC0EBD.14215619062005@msnews.microsoft.com...
> one way:
>
> =SUMPRODUCT(--(A1:A6<15),--(A1:A6<>0))
>
> In article <ifkte.15030\$R21.9732@lakeread06>, "Mike" <windme@cox.net>
> wrote:
>
>> 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

6. ## Re: =Countif (not zero)

Here are two ways:

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

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

Hope this helps.

"Mike" <windme@cox.net> wrote in message
> 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
>
>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1