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.


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.

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