Count Numbers in a Range in Excel 2007

image 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.

countifs01

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.

countifs02

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.

countifs03

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.

countifs04

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

_________________

You may also like...

4 Responses

  1. John says:

    I am 4 days into Excel, with a problem: I have 880,000; 856,000; 836,000 as numbers ranging between. e.g: 856,000 to 880,000
    880,000 represents other values of, for example: 160; 178; 188; 155;171;181
    I can get 880,00 to produce the values:160; 178; 188; 155;171;181, but the range between gives me #N/A error.
    I am using:=VLOOKUP(B1,B2:I22,3,FALSE)
    880000
    880000 160 178 188 155 171 181
    858000 159 176 187 153 170 180
    836000 157 174 185 152 168 178
    814000 155 171 183 149 165 176
    792000 152 168 180 147 162 174
    770000 149 165 177 144 159 171
    748000 147 162 175 141 156 169
    726000 144 158 172 138 152 166
    704000 141 155 169 135 149 163
    682000 137 151 166 132 146 160
    660000 134 148 164 129 142 158
    638000 131 144 161 126 139 155
    616000 128 141 158 123 135 152
    594000 124 137 155 119 132 149
    572000 121 133 153 115 127 146
    550000 117 130 150 111 124 143
    528000 113 126 147 107 121 141
    506000 109 123 144 104 117 138
    484000 105 119 142 100 113 136
    462000 101 115 137 96 110 133
    440000 96 111 136 92 106 130
    Is this solvable with Excel 2007, please?

  2. Jan says:

    Hi John,

    Your problem is not completely clear to me but

    probably the following will help you because as I understand you’re not looking for an exact match.

    Please try to sort the range in ascending order and don’t use the fourth argument in the vlookup function or TRUE instead of FALSE. That will give you the approximate match.

    Your formula: =VLOOKUP(B1,B2:I22,3,FALSE)

    Try:

    =VLOOKUP(B1,B2:I22,3)
    =VLOOKUP(B1,B2:I22,3,TRUE)
    =VLOOKUP(B1,B2:I22,3,1)

    All three with the same result.

    BTW if you need to use FALSE you can also replace this with a 0 (zero).

    Cheers,

    Jan Bolhuis
    http://www.ExcelXL.nl

    In Dutch :)

  3. Scott McMan says:

    My formula currently looks like this: =COUNTIFS($F$16:$F$601,”>=” & 18.5,$F$16:$F$601,”<=" & 20.5)

    I'd like to add on column G, so that I can get a count for both columns without doing a separate formula in another cell. How would I incorporate coulmn G data into the above formula?

    • @Scott, you could use SUM to get a total of two COUNTIFS formulas:
      =SUM(COUNTIFS($F$16:$F$601,”>=” & 18.5,$F$16:$F$601,”< =" & 20.5), COUNTIFS($G$16:$G$601,">=” & 18.5,$G$16:$G$601,”<=" & 20.5))

Leave a Reply to John Cancel reply

Your email address will not be published. Required fields are marked *