Excel COUNTIF function: count cells greater than a certain number

COUNTIF is a powerful tool that counts the number of cells within a specified range that meet a certain condition. It’s like asking Excel, “How many times does X appear within this group of cells?”

Here’s how to use COUNTIF to count cells greater than a certain number in Excel:

Syntax:

=COUNTIF(range, ">number")


Steps:

1. Open Excel: Launch Excel and open the spreadsheet containing the data you want to analyze.
2. Enter the Formula: Select the cell where you want the count result to appear.
3. Input the Formula: Type or enter the COUNTIF formula into the selected cell.
4. Example:
   Let’s say you want to count cells in the range A1:A10 that are greater than 50. Your formula would be:
   =COUNTIF(A1:A10, ">50")
5. Press Enter: After entering the formula, press Enter to execute it.

The cell will display the count of cells within the specified range that are greater than the given number.

Ensure the syntax (“>number”) is accurately used in the formula, as it concatenates the > symbol with the specified number to create the comparative condition for counting cells in Excel.

Other Comparison Operators in  Excel countif

You can use different comparison operators for various conditions:

=COUNTIF(range, "<number") for values less than a number.
=COUNTIF(range, "<=number") for values less than or equal to a number.
=COUNTIF(range, ">=number") for values greater than or equal to a number.
=COUNTIF(range, "<>number") for values not equal to a number.

countif with cell References in Excel

In case of a cell reference, you have to enclose the operator in quotes and add an ampersand (&) before the cell reference.

Suppose you have sales figures in column B and a target sales amount in cell E2. To count the number of sales that meet or exceed the target, use this formula:

=COUNTIF(B2:B10, ">="&E2)

This counts cells in B2:B10 that are greater than or equal to the value in E2.

You can combine cell references with comparison operators to create more dynamic criteria:

=COUNTIF(B2:B10, "<"&E2)

counts values less than the value in E2.

=COUNTIF(B2:B10, "<>"&E2)

counts values not equal to the value in E2.

Using Text Criteria:

To count cells based on text criteria from a cell reference, enclose the cell reference in double quotes:

=COUNTIF(A2:A10, "="&D2)

counts cells in A2:A10 that exactly match the text in D2.

Handling Ranges in Cell References:

If your criteria involves a range of values, use the ampersand operator (&) to concatenate cell references:

=COUNTIFS(B2:B10, ">="&E2, B2:B10, "<"&F2)

counts values between E2 and F2 (inclusive).