The `COUNTIF` function in Excel is used to count the number of cells in a range that meet a specific condition, known as a criterion. Here’s a step-by-step guide on how to use it:
Basic Syntax
The basic syntax for `COUNTIF` is:
COUNTIF(range, criteria)
- `range`: This is the group of cells that you want Excel to evaluate.
- `criteria`: This defines the condition that must be met for a cell to be counted. The criteria can be a number, expression, text, or a cell reference.
Examples
- Counting Text Matches:
Suppose you have a list of items in cells A1 to A10, and you want to count how many times “Apple” appears.
=COUNTIF(A1:A10, "Apple")
- Counting Numbers Greater Than a Value:
If you have numbers in cells B1 to B10 and want to count how many are greater than 50:
=COUNTIF(B1:B10, ">50")
- Counting Based on Cell Reference:
To count the number of times a value equal to the one in cell C1 appears in range A1 to A10:
=COUNTIF(A1:A10, C1)
- Using Wildcards:
- Question Mark (?): Represents a single character.
- Asterisk (*): Represents any sequence of characters.
To count all the cells in A1 to A10 that start with “Ap”:
=COUNTIF(A1:A10, "Ap*")
- Counting Blank Cells:
If you want to count the blank cells in a particular range, say A1 to A10:
=COUNTIF(A1:A10, "")
- Counting Non-Blank Cells:
To count non-blank cells in the range A1 to A10:
=COUNTIF(A1:A10, "<>")
Key Points:
- Case Sensitivity: `COUNTIF` is not case-sensitive when matching text.
- Relational Operators: Use quotes to enclose operators like `”>”`, `”<“`, `”<>”`, and so on.
Feel free to adjust the range and criteria as needed for your specific data and requirements.
Unlock Your Potential
Excel
Access
Power BI
