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?
In Excel 2007 and higher, you can 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.
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. 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 a 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 Video
To see the steps for creating a COUNTIF and a COUNTIFS formula, please watch these 2 short Excel video tutorials.
Video: Count Numbers in a Range with COUNTIF
Video: Count Numbers in a Range with COUNTIFS
Excel Tweet of the Day
Yeah, I've felt like this guy too:
I think I just installed 100TB of microsoft products in order to be able to spend a couple of hours reviewing a spreadsheet.
For more entertaining and enlightening Excel tweets, culled from the thousands posted every day, see
_________________
