+ Reply to Thread
Results 1 to 7 of 7

count specific values in a range

Hybrid View

  1. #1
    Registered User
    Join Date
    11-15-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    count specific values in a range

    I need to count values in a range of values that score above 18. The values aren't consecutive (e.g. a, c, e, g). Once they have counted the values in this range that score above 18 I need it to return a value of 0 if count is greater than 2 and 1 if less than 2.

    eg.
    a c e g
    2 19 3 24 There are 2 scores above 18 therefore the answer is 0.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: count specific values in a range

    And what is returned if the count is =2 ?

  3. #3
    Registered User
    Join Date
    11-15-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: count specific values in a range

    sorry 0 as well.

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: count specific values in a range

    Will the values always be separated by one column or is there no pattern ?

  5. #5
    Registered User
    Join Date
    11-15-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: count specific values in a range

    For some yes but the rest there are two spaces. The exact order is below:

    see attached for actual values. The columns with numbers are the ones in consideration.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: count specific values in a range

    Perhaps =IF(COUNTIF(A2:P2,">18")<2,1,0)

  7. #7
    Registered User
    Join Date
    11-15-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: count specific values in a range

    awesome that works - thanks heaps. I was trying to make it too complicated.

+ 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