The COUNTIF function with example in Excel

The COUNTIF function in Excel is used to count the number of cells within a range that meet a specified condition.

Syntax

excel
COUNTIF(range, criteria)

• range: The range of cells that you want to apply the criteria to.
• criteria: The condition that defines which cells to count. It can be expressed as a number, expression, cell reference, or text string.

Example

Suppose you have a list of numbers in cells A1 to A5, and you want to count how many of these numbers are greater than 10.

Data in Column A:

css
A
1    8
2    15
3    12
4    5
5    20

1. Using the COUNTIF Function:
   • Formula: =COUNTIF(A1:A5, ">10")
   • Result: 3
   • Explanation: There are 3 numbers in the range A1
     that are greater than 10.

Step-by-Step Example

1. Input Data:

   • In cells A1 to A5, enter the following values:

     8
     15
     12
     5
     20
     

2. Using COUNTIF Function:

   • In cell B1, enter the formula:

     excel
     =COUNTIF(A1:A5, ">10")
     

   • Press Enter.

3. Result:

   • The result in cell B1 will be 3, indicating that there are 3 numbers greater than 10 in the range A1
     .

Example with Text Criteria

You can also use text criteria with the COUNTIF function.

Data:

css
A
1    Apple
2    Banana
3    Apple
4    Orange
5    Apple

1. Using the COUNTIF Function with Text Criteria:
   • Formula: =COUNTIF(A1:A5, "Apple")
   • Result: 3
   • Explanation: There are 3 occurrences of "Apple" in the range A1
     .

Step-by-Step Example with Text Criteria

1. Input Data:

   • In cells A1 to A5, enter:

     mathematica
     Apple
     Banana
     Apple
     Orange
     Apple
     

2. Using COUNTIF Function:

   • In cell B1, enter the formula:

     excel
     =COUNTIF(A1:A5, "Apple")
     

   • Press Enter.

3. Result:

   • The result in cell B1 will be 3, indicating that there are 3 occurrences of "Apple" in the range A1
     .

Example with Criteria from Another Cell

You can also use criteria from another cell.

Data:

css
A
1    8
2    15
3    12
4    5
5    20
6    10

Criteria in Cell B1:

shell
>10

1. Using the COUNTIF Function with Criteria from Another Cell:
   • Formula: =COUNTIF(A1:A6, ">"&B1)
   • Result: 3
   • Explanation: There are 3 numbers greater than 10 in the range A1
     .

Step-by-Step Example with Criteria from Another Cell

1. Input Data:

   • In cells A1 to A6, enter:

     8
     15
     12
     5
     20
     10
     

   • In cell B1, enter: >10

2. Using COUNTIF Function:

   • In cell C1, enter the formula:

     excel
     =COUNTIF(A1:A6, ">"&B1)
     

   • Press Enter.

3. Result:

   • The result in cell C1 will be 3, indicating that there are 3 numbers greater than the value in cell B1 (which is 10) in the range A1
     .

Conclusion

The COUNTIF function in Excel provides a flexible way to count cells based on a specified condition. It can be used with various types of criteria, including numbers, text, logical expressions, and even criteria from other cells. This makes it a powerful tool for data analysis and reporting tasks in Excel.