COUNTIF function

The COUNTIF function in Excel counts the number of cells in a range that meet a specific criteria. It is useful for counting cells that contain certain values, or that satisfy specific conditions, such as numbers greater than a certain value or text matching a pattern.

Syntax:

COUNTIF(range, criteria)

Arguments:

• range: The range of cells to evaluate.
• criteria: The condition or test that determines which cells to count. The criteria can be:
  • A number (e.g., 10),
  • Text (e.g., "apple"),
  • A logical expression (e.g., ">5"),
  • A cell reference (e.g., A1).

Example 1: Counting Numbers Greater Than a Certain Value

If you have a range A1:A10 with the values:

A1: 5
A2: 8
A3: 12
A4: 4
A5: 6
A6: 10
A7: 3
A8: 11
A9: 7
A10: 9

To count how many numbers are greater than 6, you would use the formula:

=COUNTIF(A1:A10, ">6")

This will return 6, as there are 6 values greater than 6 in the range.

Example 2: Counting Cells Containing Specific Text

If you have a range B1:B5 with the values:

B1: "Apple"
B2: "Banana"
B3: "Apple"
B4: "Orange"
B5: "Apple"

To count how many cells contain the text “Apple”, you would use the formula:

=COUNTIF(B1:B5, "Apple")

This will return 3, as “Apple” appears three times in the range.

Example 3: Using a Cell Reference for Criteria

If cell C1 contains the number 10, you can count how many values in the range A1:A10 are greater than the value in C1:

=COUNTIF(A1:A10, ">" & C1)

This will count how many numbers in A1:A10 are greater than 10, depending on the value in C1.

Key Points:

• COUNTIF only allows one condition. If you need multiple conditions, you would use the COUNTIFS function.
• Criteria can include wildcards like * (any number of characters) or ? (any single character) for text matching.
• The function works for both numeric and text data types.

Use Cases:

• Counting cells that meet specific conditions, such as counting the number of times a particular product appears in a list.
• Analyzing numerical data, like counting how many values are above or below a threshold.