Contextures

Home > Formulas > Count > If Greater Than

Excel Count If Greater Than

Use Excel COUNTIFS function to count number cells if greater than set amount. Or count cells if values are less than a set amount, or between a minimum and maximum amount. Short videos, Excel files, written steps

count if greater than with COUNTIFS function

Count If Greater Than Or Less Than

In your Excel file, you might need to count numbers based on criteria. For example, check for numbers with a value:

  • greater than 10
  • less than 15
  • between 10 and 15

To get those counts, you can use one of the following functions:

  1. COUNTIFS function (recommended)
  2. COUNTIF function (older versions of Excel)

Use COUNTIFS or COUNTIF?

To count based on criteria, I recommend that you use the COUNTIFS function, instead of the older function, COUNTIF.

  • COUNTIFS function allows 1 or more criteria
    • COUNTIFS formulas with multiple criteria are easy to understand
    • Works in Excel 2007 and later versions
  • COUNTIF function allows only 1 criterion
    • COUNTIF formulas with multiple criteria are complicated, and require multiple COUNTIF functions
    • Works in any version of Excel, including Excel 2003 and earlier

Use COUNTIFS function

Unless you're using Excel 2003 or earlier, here's what I recommend:

  • For new formulas, always use the COUNTIFS function, even if the calculation only needs 1 criterion.
  • However, if a workbook has older formulas, that use the COUNTIF function, you can leave those as is.

Video: Count If Greater Than

In this short video, I show how to count cells that contain a number greater than 20. Next, change the formula slightly, to count cells with a value of 20 or greater.

Count If Greater Than

In this example only the rows where the quantity is greater than 20 will be counted.

  • First, select the cell where you want the formula (cell A12 in this example)
  • Then, to start the formula, type:   =COUNTIFS(
  • Next, select the cells that contain the values to check for the criterion.
    • In this example, cells B2:B10 will be checked
  • Type a comma, then type the criterion: ">20"
    • The > operator (greater than) is used before the number
    • Entire criterion is enclosed in double quote marks: ">20"
  • Type a closing bracket, and then press the Enter key

The completed formula is:
          =COUNTIFS(B2:B10,">20")

count if greater than with COUNTIFS function

Count If Greater Than Or Equal To

In this example only the rows where the quantity is greater than or equal to 20 will be counted.

  • First, select the cell where you want the formula (cell A12 in this example)
  • Then, to start the formula, type:   =COUNTIFS(
  • Next, select the cells that contain the values to check for the criterion.
    • In this example, cells B2:B10 will be checked
  • Type a comma, then type the criterion: ">=20"
    • The >= operator (greater than or equal to) is used before the number
    • Entire criterion is enclosed in double quote marks: ">=20"
  • Type a closing bracket, and then press the Enter key

The completed formula is:
          =COUNTIFS(B2:B10,">=20")

Comparing Criteria Results

In the screen shot below, I've compared the formula results:

  • The ">20" formula result in D2 is 7
    • Does not count the 20 in cell B8
  • The ">=20" formula result in cell D6 is 8
    • Does count the 20 in cell B8

count if less than with COUNTIFS function

Count If Less Than

In this example only the rows where the quantity is less than 20 will be counted.

  • First, select the cell where you want the formula(cell D2 in this example)
  • Then, to start the formula, type:   =COUNTIFS(
  • Next, select the cells that contain the values to check for the criterion.
    • In this example, cells B2:B10 will be checked
  • Type a comma, then type the criterion: "<20"
    • The < operator (less than) is used before the number
    • Entire criterion is enclosed in double quote marks: "<20"
  • Type a closing bracket, and then press the Enter key

The completed formula is:
          =COUNTIFS(B2:B10,"<20")

count if less than with COUNTIFS function

Count If Less Than Or Equal To

In this example only the rows where the quantity is less than or equal to 20 will be counted.

  • First, select the cell where you want the formula (cell D6 in this example)
  • Then, to start the formula, type:   =COUNTIFS(
  • Next, select the cells that contain the values to check for the criterion.
    • In this example, cells B2:B10 will be checked
  • Type a comma, then type the criterion: "<=20"
    • The <= operator (less than or equal to) is used before the number
    • Entire criterion is enclosed in double quote marks: "<=20"
  • Type a closing bracket, and then press the Enter key

The completed formula is:
          =COUNTIFS(B2:B10,"<=20")

Comparing Criteria Results

In the screen shot below, I've compared the formula results:

  • The "<20" formula result in D2 is 3
    • Does not count the 20 in cell B6
  • The "<=20" formula result in cell D6 is 4
    • Does count the 20 in cell B6

count if less than with COUNTIFS function

Count If - Between Min and Max

To count numbers in a specific range, such as "between 5 and 10", you can use a COUNTIFS formula, with 2 criteria.

In the example below, the COUNTIFS formulas will count rows where the number in column B is between 20 and 35.

This formula will use 2 criteria sets, to check :

  1. Minimum: Is the number greater than [or equal to] 20
    • AND
  2. Maximum: Is the number less than [or equal to] 35

COUNTIFS Formula

Enter one of the following formulas in the cell where you want the count -- I used cells D2 and D8 in the screen shot below.

  • NOTE: There are 2 versions of the formula, so you can choose whether to include the minimum and maximum numbers, or exclude those numbers.

A) Exclude the minimum and maximum from the count

  • =COUNTIFS(B2:B10,">20",B1:B10,"<35")
    • Result (cell D2) is 3

B) Include the minimum and maximum in the count

  • =COUNTIFS(B2:B10,">=20",B1:B10,"<=35")
    • Result (cell D8) is 5

count if greater than with COUNTIFS function

Cell References for Criteria

Instead of typing the criterion in a formula, you can refer to a worksheet cell, where the criteria values are stored. This makes it easier to adjust the number range - you don't have to modify the formula, just the worksheet cells.

For example, the COUNTIFS formula above could be changed to:

  • =COUNTIFS(B1:B10,">=" & B12, B1:B10,"<=" & B13)

In this example:

  • minimum number was typed in cell B12
  • maximum number was typed in cell B13 .

countifs formula with criteria in worksheet cells

Count Criteria with COUNTIF

For Excel 2003 and earlier, or in any version of Excel, you can use the COUNTIF function to count with criterial

In the video below, I show how to use the Excel COUNTIF function to count the number of items in a list that are over or under a specific amount. Written steps are below the video.

Video Timeline

  • 0:00 Student Grades List
  • 0:51 Grades Less Than 50
  • 2:18 Grades Greater Than or Equal 50
  • 3:34 Use Cell Reference

COUNTIF Greater Than or Equal to

You can use an operator with a criterion. In this example only the rows where the quantity is greater than or equal to ten will be counted.

  • First, select the cell where you want the formula (cell A12 in this example)
  • Then, to start the formula, type:   =COUNTIF(
  • Next, select the cells that contain the values to check for the criterion.
    • In this example, cells B1:B10 will be checked
  • Type a comma, then type the criterion: ">=10"
    • In this example, you're checking for rows where the quantity is greater than or equal to 10.
    • The >= operator is used before the number, and the entire criterion is enclosed in double quotes: ">=10"
  • Type a closing bracket, then press the Enter key
  • The completed formula is:
              =COUNTIF(B1:B10,">=10")

Count Excel criteria operator

COUNTIF - Cell References

Instead of typing the criterion in a formula, you can refer to a cell.

For example, the formula in step 8 above could be changed to:
    =COUNTIF(B1:B10,">=" & B12)
if cell B12 contained the number — 10

Or, you could use a function as part of the criterion.

For example, count cells where the date is before today (less than): 
    =COUNTIF(A1:A10,"<"&TODAY())   ▲TOP

Count Numbers in Range - COUNTIF

With the COUNTIF function, if you need to get the count based on a number range, you'll need a formula that uses 2 instances of COUNTIF.

In this example, the COUNTIF formula will count rows where the quantity is greater than or equal to 5 and less than or equal to 10

Create COUNTIF Formula

Enter the following formula in the cell where you want the count -- I used cell B12 in the screen shot below. The criteria and operators are enclosed in double quote marks.

  • =COUNTIF(B1:B10,">=5") - COUNTIF(B1:B10,">10")

The formula result is 6, because there are:

  • 9 products with quantity greater than or equal to 5
  • 3 products with quantity greater than 10
  • Subtract the 3 numbers that are too high: 9-3 = 6

countif numbers in number range

Change Operator for COUNTIF Function

Instead of typing the operator into the COUNTIF formula, as shown above, you can create a list of all possible operators, and select one from a drop down list. Then, refer to that operator in the formula.

This video shows the steps for setting up the formula, and the written instructions are below the video.

Create a Drop Down List of Operators

To create a drop down list operators:

  1. On a different sheet in the workbook, type a list of operators in a column.
  2. Select the cells in the list, and name that list as OpList.

    countif operator list

  3. On the main sheet, select the cell where you want the drop down list -- cell E6 in this example
  4. Use the data validation command to create the drop down list, based on the named range -- OpList

countif operator list

Use the Drop Down List

Change your COUNTIF formula, to replace the typed operator with a reference to the cell with the drop down list.

=COUNTIF(B2:B11,E6&F6)

Then, select one of the operators from the drop down list in cell E6, and the formula result will change.

countif operator list

Get the Excel File

  1. Download the Count If Greater Than sample workbook. It has the Greaterh Than and Less Than examples from this page. The zipped file is in xlsx format, and does not contain any macros.

More Function Tutorials

Count Functions

SUM / SUMIF

Filtered Rows, Count

Date Range, Sum or Count

Count Criteria in Other Column

 

 

Last updated: January 24, 2024 7:49 PM