Count numbers greater than or less than a number

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

To count numbers greater than or less than a number, use the COUNTIF function. To count numbers that fall between a range of numbers, you can use the SUMPRODUCT function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.
    Important    Do not select the row or column headers.
    Selecting an example from Help
  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 
1
2
3
4
5
6
7
A B
Salesperson Invoice
Buchanan 15,000
Buchanan 9,000
Suyama 8,000
Suyama 20,000
Buchanan 5,000
Dodsworth 22,500
Formula Description (Result)
=COUNTIF(B2:B7,">9000") Numbers above 9000 (3)
=COUNTIF(B2:B7,"<=9000") Numbers less than or equal to 9000 (3)
=SUMPRODUCT((B2:B7>=9000)*(B2:B7<=22500)) The SUMPRODUCT function counts the number of cells in the range B2:B7 that contain numbers greater than or equal to 9000 and less than or equal to 22500 (4).

Function details

The COUNTIF function counts the number of cells within a range that meet a single criterion (condition) that you specify.

=COUNTIF(range, criteria)

The SUMPRODUCT function can be used to count numbers by specifying multiple ranges and multiple criteria. In the example, B2:B7 is the range used for each criterion, which are >=9000 and B2:B7<=22500.

For more information, see COUNTIF and SUMPRODUCT.

 
 
Applies to:
Excel 2003