How to Use the COUNTIF Function in Excel

The COUNTIF function in Excel is a versatile tool used to count the number of cells that meet a specific criterion within a range. Here’s a breakdown of how to use it:



=COUNTIF(range, criteria)


  • range: The range of cells you want to search within.
  • criteria: The condition you want to apply to the cells in the range. This can be a number, text, logical expression,or a reference to another cell containing the criteria.


  • Count the number of cells containing the value “apple” in the range A1:A10:


=COUNTIF(A1:A10, "apple")

  • Count the number of cells greater than 10 in the range B2:B20:


=COUNTIF(B2:B20, ">10")

  • Count the number of cells containing the text “orange” or “banana” in the range C3:C15:


=COUNTIF(C3:C15, {"orange","banana"})

Additional Notes:

  • You can use wildcards in your criteria, such as “*” to match any number of characters or “?” to match a single character.
  • You can combine multiple criteria using logical operators like AND and OR.
  • The COUNTIF function is case-sensitive by default. To perform a case-insensitive search, use the UPPER or LOWER function in conjunction with COUNTIF.

Here are some advanced tips for using the COUNTIF function:

  • Use absolute cell references: When copying the formula to other cells, ensure the range reference remains fixed if it’s absolute (e.g., $A$1:$A$10).
  • Combine COUNTIF with other functions: You can use COUNTIF with other functions like SUMIF or AVERAGEIF to perform more complex calculations based on specific criteria.
  • Use COUNTIFS for multiple criteria: If you need to count cells meeting multiple criteria, use the COUNTIFS function, which allows you to specify multiple range-criteria pairs.

I hope this explanation helps! Let me know if you have any other questions about using the COUNTIF function in Excel.

Leave a Reply

Your email address will not be published. Required fields are marked *