The `COUNTIFS` function in Excel is used to count the number of cells that meet multiple criteria across different ranges. Each range and criterion is paired, and the function can handle multiple pairs. Here’s how to use it:
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- criteria_range1: This is the first range in which you want to evaluate the associated criteria.
- criteria1: The criteria to use on `criteria_range1`. This can be a number, expression, cell reference, or text.
- [criteria_range2, criteria2], …: Optional additional ranges and their associated criteria.
Key Points
- All criteria ranges must be of the same size, otherwise, Excel will return a `#VALUE!` error.
- `COUNTIFS` can handle up to 127 range/criteria pairs.
- Criteria can include wildcards: `*` (any sequence of characters) and `?` (any single character).
Example
Suppose you have a dataset of sales with columns for product type, sales representative, and sales amount. You want to count the number of times a particular product was sold by a specific sales representative.
Example Data:
- A2:A10: Product Type
- B2:B10: Sales Representative
- C2:C10: Sales Amount
Use Case
Count how many times “Product A” was sold by “John”:
=COUNTIFS(A2:A10, "Product A", B2:B10, "John")
Practical Example
Suppose you also want to include a condition for sales amount greater than $100:
=COUNTIFS(A2:A10, "Product A", B2:B10, "John", C2:C10, ">100")
Things to Watch Out For
- Data Consistency: Ensure all criteria ranges have the same number of rows/columns.
- Criterion Types: Make sure your criteria are in the correct form; use quotes for text and expressions.
- Complex Criteria: For more complex logic, you might need to explore using functions like `SUMPRODUCT`.
By using `COUNTIFS`, you can conduct detailed analysis across multiple conditions, making it a powerful tool for data evaluation in Excel.
Unlock Your Potential
Excel
Access
Power BI
