Solving Excel COUNT Problems with COUNTIFS Function (practice file included)

BY 1bbeh

July 14, 2025•

Public

Private

5477 views

Excel Tutorial Note: COUNTIFS Function

A comprehensive tutorial on how to use Excel's COUNTIFS function, including tips, common pitfalls, and advanced scenarios.

COUNTIFS counts the number of cells that meet one or more criteria.
More versatile than COUNTIF, which allows only a single condition.
Useful for dynamic business requirements where multiple conditions might be added later.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Task: Count how many times "Shirt white" appears in the Product column.
- Use cell reference, e.g.:
```
=COUNTIFS(ProductRange, "Shirt white")
```
- Using a cell reference is preferable for flexibility.

Task: Count "Shirt white" in February.
- Can be done in any order, just ensure correct range for each criterion.
- Example:
```
=COUNTIFS(ProductRange, "Shirt white", MonthRange, "February")
```
- All selected ranges must have the same height (number of rows).

Task: Add Country = "USA".

Example:

=COUNTIFS(ProductRange, "Shirt white", MonthRange, "February", CountryRange, "USA")

Problem: COUNTIFS by default is AND logic. To count "Shirt white" OR "Shirt blue":
- Use separate COUNTIFS and add results:
```
=COUNTIFS(ProductRange, "Shirt white") + COUNTIFS(ProductRange, "Shirt blue")
```
- Shortcut: Use SUM with an array for multiple criteria (and use Ctrl+Shift+Enter in Excel 2016 or earlier):
```
=SUM(COUNTIFS(ProductRange, {"Shirt white","Shirt blue"}))
```

Task: Count products not equal to "Shirt yellow".
- Use "<>" for not equal (in quotes):
```
=COUNTIFS(ProductRange, "<>Shirt yellow")
```
- For cell references:
```
=COUNTIFS(ProductRange, "<>" & A1)
```
  where A1 contains "Shirt yellow".

Task: Sales greater than 550.
- Combine operator with ampersand and reference:
```
=COUNTIFS(SalesRange, ">" & 550)
```
- Greater than or equal:
```
=COUNTIFS(SalesRange, ">=" & 550)
```

Blank: Use empty quotation marks:
```
=COUNTIFS(CountryRange, "")
```
Non-blank: Use "<>" with quotation marks:
```
=COUNTIFS(CountryRange, "<>")
```

Task: Count orders between two dates.
- Use greater than start date and less than end date (use ampersand with cell references):
```
=COUNTIFS(DateRange, ">" & StartDateCell, DateRange, "<" & EndDateCell)
```
- To include the boundary dates, use ">=" and "<=".

Contains: Use "*" at both ends:

=COUNTIFS(ProductRange, "*" & "blue" & "*")

Task: Products contain "blue" AND month is "February":

=COUNTIFS(ProductRange, "*" & "blue" & "*", MonthRange, "February")

Prefer COUNTIFS over COUNTIF for flexibility.
All criteria range arguments must have the same length.
Use cell references for criteria when possible for easier updates.
Use SUM and array constants for OR logic.
Wildcards (*) help with partial text matches.
Use ampersand (&) to concatenate operators and cell references in criterions for dynamic formulas.

COUNTIFS is a robust tool for data analysis in Excel.
Use it for single/multiple conditions, partial matches, and combining AND/OR logic when needed.
Experiment with different criteria for dynamic and powerful data calculations.

Further Resources:
Download the example file from the video description for follow-along practice.

If you found this helpful, leave feedback and suggestions for more content!