# COUNTIF where value between a range

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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 :-)

7. ## 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. ## Re: COUNTIF where value between a range

Originally Posted by Pete_UK
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. ## 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

##### Users Browsing this Thread

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