Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif with criteria range (excel 2000)

    Hi lounge,

    ilooked at previous posts concering the countif could not find what I was looking for right away so here is my question

    How do you make the countif function count values greater than 70 and less and equal to 85 ?

    Countif(Range,Criteria)

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: countif with criteria range (excel 2000)

    =COUNTIF(Range,"<=85")-COUNTIF(Range,"<=70")

    or

    =SUMPRODUCT((Range<=85)*(Range>70))

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with criteria range (excel 2000)

    thank you

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with criteria range (excel 2000)

    What if I want to use a cell reference in place of the absolute criterion value? Something like:
    =COUNTIF(Range,"<sheet2!A10")
    Another post gave me the hint to add an "=" sign after the quote, thus:
    =COUNTIF(Range,"=<sheet2!A10")
    and that helps in that XL swallows the formula as a valid formula but it doesn't give me the correct count.

    Any clues..?

    Ken

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: countif with criteria range (excel 2000)

    Try
    <code>
    =COUNTIF(Range,"<"&Sheet2!A10)
    </code>
    Say for example that Sheet2!A10 contains 37, then the condition "<"&Sheet2!A10 is evaluated to "<37".

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with criteria range (excel 2000)

    <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    and that also allows me to add in a negative sign as well:
    =COUNTIF(Range,"<"&"-"&Sheet2!A10)

    <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    Ken

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: countif with criteria range (excel 2000)

    Or even
    <code>
    =COUNTIF(Range,"<-"&Sheet2!A10)</code>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: countif with criteria range (excel 2000)

    Instead of:
    =COUNTIF(Range,"<"&"-"&Sheet2!A10)

    You could use:
    =COUNTIF(Range,"<-"&Sheet2!A10)

    And save some keystrokes...

    Steve

Posting Permissions

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