Count Numbers Between X and Y – Excel COUNTIF COUNTIFS

Sometimes you need to count the number of items that are in a range, such as between 5 and 10. For example:

• How many students got a grade between 50 and 70?
• How many golfers scored between 70 and 80?
• How many orders were for a quantity between 5 and 10?

Excel COUNTIF and COUNTIFS Functions

In Excel 2007 and higher, use the COUNTIF and COUNTIFS functions to calculate the answers to those questions.

In earlier versions, COUNTIFS is not available, and you can use COUNTIF.

Count Numbers in a Range with COUNTIF

In the worksheet shown below, there’s a list of items ordered, and the quantity of each item. The challenge is to count the orders where the quantity is between 5 and 10.

Unfortunately, Excel doesn’t have a Between function, so you’ll have to find another solution.

Two COUNTIF Formulas

With two separate COUNTIF formulas, you can calculate the number of orders where the quantity is 5 or greater, and the number of orders where the quantity is greater than 10.

Then, you can combine those two COUNTIF formulas, to find the answer. The first formula shows that there are 9 orders with a quantity of 5 or higher.

Subtract Number over 10

From that amount, you’ll subtract the number of orders with a quantity greater than 10 (3 orders). Those shouldn’t be included in the final count, because they’re over the maximum.

=COUNTIF($B$2:$B$10,”>=” & E4) – COUNTIF($B$2:$B$10,”>” & G4)

The combined formulas show a result of 6 — the number of orders with a quantity between 5 and 10.

Count Numbers in Range with COUNTIFS

In Excel 2007, and later versions, you can use the COUNTIFS function, and the formula is a bit simpler. Instead of using two COUNTIF functions, and subtracting one result from the other, you’ll list all the ranges and criteria within one COUNTIFS formula. The result will be a count of the orders that meet all the criteria.

In this example you’re counting the orders with a quantity between 5 and 10. Using the Excel operators, you want quantities that are >=5 AND<=10.

The COUNTIFS formula uses pairs of ranges and criteria, with a limit of 127 pairs. You’ll use two — the first to check for quantity >=5 and the second to test for quantity <=10.

=COUNTIFS($B$2:$B$10,”>=” & E4,$B$2:$B$10,”<=” & G4)

The COUNTIFS formula shows a result of 6 — the same as the COUNTIF formula that you created earlier.

Add More Criteria to COUNTIFS

It’s easy to add more criteria to the COUNTIFS function. Currently, the COUNTIFS formula shows that 6 of the orders have a quantity between 5 and 10. You can add another range and criteria, to find only the Pen orders.

=COUNTIFS($B$2:$B$10,”>=” & E4,$B$2:$B$10,”<=” & G4,$A$2:$A$10,”Pen”)

You can also use wildcards in the criteria, so the following formula would count any orders where the item name starts with “Pen”.

=COUNTIFS($B$2:$B$10,”>=” & E4,$B$2:$B$10,”<=” & G4,$A$2:$A$10,”Pen*”)

Watch the Count Numbers in a Range Videos

To see the steps for creating a COUNTIF and a COUNTIFS formula, you can watch these two short Excel videos.

The best solution is to use the Excel COUNTIFS function, if you need to count numbers in a specific range, between a minimum and maximum number.

For older versions of Excel, where the COUNTIFS function is not available, use the Excel COUNTIF function. The video below shows how to combine two COUNTIF functions in a single formula, to count numbers in a specific range.

More COUNTIFS Examples

Excel Count and Sum in Date Range

Excel Count If Greater Than or Less Than

_________________