+ Reply to Thread
Results 1 to 6 of 6

=Countif (not zero)

  1. #1
    Mike
    Guest

    =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. #2
    Ragdyer
    Guest

    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
    news:ifkte.15030$R21.9732@lakeread06...
    > 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. #3
    JE McGimpsey
    Guest

    Re: =Countif (not zero)

    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


  4. #4
    Bill Kuunders
    Guest

    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
    news:ifkte.15030$R21.9732@lakeread06...
    > 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. #5
    Mike
    Guest

    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. #6
    Lewis Clark
    Guest

    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
    news:ifkte.15030$R21.9732@lakeread06...
    > 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 Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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