Home > Formulas > Count > If Greater Than Excel Count If Greater ThanUse 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 Or Less ThanIn your Excel file, you might need to count numbers based on criteria. For example, check for numbers with a value:
To get those counts, you can use one of the following functions:

Use COUNTIFS or COUNTIF?To count based on criteria, I recommend that you use the COUNTIFS function, instead of the older function, COUNTIF.
Use COUNTIFS functionUnless you're using Excel 2003 or earlier, here's what I recommend:

Video: Count If Greater ThanIn 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 ThanIn this example only the rows where the quantity is greater than 20 will be counted.
The completed formula is: 
Count If Greater Than Or Equal ToIn this example only the rows where the quantity is greater than or equal to 20 will be counted.
The completed formula is: 
Comparing Criteria ResultsIn the screen shot below, I've compared the formula results:

Count If Less ThanIn this example only the rows where the quantity is less than 20 will be counted.
The completed formula is: 
Count If Less Than Or Equal ToIn this example only the rows where the quantity is less than or equal to 20 will be counted.
The completed formula is: 
Comparing Criteria ResultsIn the screen shot below, I've compared the formula results:

Count If  Between Min and MaxTo 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 :

COUNTIFS FormulaEnter one of the following formulas in the cell where you want the count  I used cells D2 and D8 in the screen shot below.
A) Exclude the minimum and maximum from the count
B) Include the minimum and maximum in the count

Cell References for CriteriaInstead 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:
In this example:

Count Criteria with COUNTIFFor 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

COUNTIF  Cell ReferencesInstead 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: Or, you could use a function as part of the criterion. For example, count cells where the date is before today (less than): 
Count Numbers in Range  COUNTIFWith 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 FormulaEnter 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.
The formula result is 6, because there are:

Change Operator for COUNTIF FunctionInstead 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 OperatorsTo create a drop down list operators:

Use the Drop Down ListChange 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. 
Get the Excel File

More Function Tutorials 
Last updated: January 24, 2024 7:49 PM