+ Reply to Thread
Results 1 to 9 of 9

COUNTIF where value between a range

  1. #1
    Registered User
    Join Date
    05-04-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    18

    COUNTIF where value between a range

    Hi

    I want a count where a value is between a range:
    COUNTIF(range, 40) + COUNTIF(range,41) + COUNTIF(range, 41)+60 etc

    I cant use COUNTIFS becacuse Java POI doesnt support it:
    https://waltercedric.com/index.php?o...102&Itemid=332

    Is there a way to specify criteria in COUNTIF using AND or BETWEEN or another approach to solve this ?

    Also I have same problem for MAXIFS to get unit price as MAXIFS is unsupported

    I want to show a summary like below by looking into the (dynamic) list of data:
    Range Count Unit Price
    100-150 2 $200
    151-200 2 $200


    Sample data
    Item Height Unit Price
    1 110 $200
    2 120 $200
    3 155 $300
    4 175 $400



    Thanks

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    5,948

    Re: COUNTIF where value between a range

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,248

    Re: COUNTIF where value between a range

    You can do it with 2 COUNTIF functions - the first should count all values above 40 (which will include those values above 60 as well), and then you should subtract the second COUNTIF which counts all values above 60, like this:

    =COUNTIF(range,">=40") - COUNTIF(range,">60")

    For the second part of your question you should be able to use a MAX(IF(… construct as an array formula. There are not many versions of XL which support MAXIFS.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    05-04-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    18

    Re: COUNTIF where value between a range

    Thanks - first solution works great.

    Can you explain how to use MAX(IF(.. get the highest (or lowest, or any) UnitPrice between a given range (they will all be the same for a given height range)

    Thanks

  5. #5
    Registered User
    Join Date
    05-04-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    18

    Re: COUNTIF where value between a range

    This is the kind of reply I give to end users when their query is too complicated and I just want them to go away :-)

  6. #6
    Registered User
    Join Date
    05-04-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    18

    Re: COUNTIF where value between a range

    This is the kind of reply I give to end users when their query is too complicated and I just want them to go away :-)
    Last edited by ASPGranpapa; 12-07-2018 at 11:48 AM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,248

    Re: COUNTIF where value between a range

    It's difficult to talk in generalities - I prefer to have actual cell and column references to illustrate the formula. Basically, you would have something like this:

    =MAX(IF((criteria1_range = criteria1)*(criteria2_range = criteria2),max_range))

    and this should be committed using Ctrl-Shift-Enter rather than the usual Enter.

    criteria_range for both criteria would in your sample data be $B$2:$B$5, and criteria1 would be >=100 and criteria2 would be <=150 (no need for the red equals sign above), The actual numbers could be replaced by cell references which contain those numbers, making the formula easier to copy down. The max_range would be $C$2:$C$5 in your example.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    05-04-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    18

    Re: COUNTIF where value between a range

    Quote Originally Posted by Pete_UK View Post
    It's difficult to talk in generalities - I prefer to have actual cell and column references to illustrate the formula. Basically, you would have something like this:

    =MAX(IF((criteria1_range = criteria1)*(criteria2_range = criteria2),max_range))

    and this should be committed using Ctrl-Shift-Enter rather than the usual Enter.

    criteria_range for both criteria would in your sample data be $B$2:$B$5, and criteria1 would be >=100 and criteria2 would be <=150 (no need for the red equals sign above), The actual numbers could be replaced by cell references which contain those numbers, making the formula easier to copy down. The max_range would be $C$2:$C$5 in your example.

    Hope this helps.

    Pete
    Works perfectly, thanks.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,248

    Re: COUNTIF where value between a range

    That's good to hear - thanks for the rep earlier.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

    Pete

+ 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