Counting Across Conditions With COUNTIFS

COUNTIFS counts how many rows satisfy several conditions at the same time. Where COUNTIF answers "how many sales were in the East?", COUNTIFS answers "how many East sales happened in January?"

It doesn't add or average anything — it simply tallies the rows that pass every test. That makes it perfect for questions like "how many orders over $100 came from new customers?"

Let's learn its structure and put it to work.

COUNTIFS has no sum_range, because it isn't adding values — it only needs the ranges to test and the criteria to test them against.

• criteria_range1, criteria1 — the first test
• criteria_range2, criteria2 — the second test
• ...and so on, up to 127 pairs

Every argument comes in a tidy range, criteria pair. The result is a single whole number: the count of rows where all conditions are true.

Using the same sales table — column A is Region, column B is Month, column C is Amount — let's count how many sales were East and in January.

There's no amount column in this formula because we're counting rows, not summing money. Each pair narrows the tally further.

Just like SUMIFS, COUNTIFS accepts comparison operators inside quotes for numeric tests.

• ">100" — values above 100
• ">=18" — 18 or older
• "<>" — any non-empty cell

Here we count East region rows where the amount is greater than 100. Notice the amount column appears as a criteria_range, not a sum_range.

A common need is counting values that fall between two numbers. You can't do this with one criterion — you give the same column twice with a lower and an upper bound.

This counts amounts that are at least 50 and at most 200. Both conditions point at column C, and because COUNTIFS uses AND logic, only rows inside the band are counted.

To make the bounds dynamic, store them in cells and join the operator with &. Put the low value in F1 and the high value in F2.

">="&F1 builds greater than or equal to F1. This lets a user type new limits and instantly see the updated count without editing the formula itself.

Two special criteria are useful for data quality checks:

• "<>" counts cells that are not empty
• "=" or "" counts cells that are empty

This formula counts how many East region rows have a salesperson filled in (column D not blank) — a quick way to spot missing data.

Text criteria support the same wildcards as SUMIFS. Use * for any run of characters and ? for a single character.

This counts every row whose salesperson name starts with the letter M — Maria, Mike, Mohammed all match. Wildcards only affect text; they're ignored against numbers.

The same dimension rule from SUMIFS applies: every criteria_range must be the same shape. If one range is A2:A100 and another is B2:B50, COUNTIFS returns a #VALUE! error.

Whole-column references like A:A and B:B are automatically the same size, which is why they're a popular, error-proof choice for criteria ranges.

COUNTIFS is the engine behind many dashboards. Lay region names down column F and month names across row 1, then count each combination with mixed references so the formula fills the whole grid.

Locking the columns with $A:$A and the lookup cells with $F2 / G$1 lets one formula populate an entire cross-tab of counts when copied.

Keep the family straight. COUNTIF takes a single range, criteria pair, while COUNTIFS takes one or more pairs. Unlike SUMIFS, neither has a separate result range — both simply count.

If you ever need more than one condition, reach for COUNTIFS. Many people use COUNTIFS even for a single test so they only have to remember one consistent pattern across SUM, COUNT, and AVERAGE.

Think about how COUNTIFS differs from SUMIFS.

You can now tally rows across many conditions:

• COUNTIFS(range1, crit1, range2, crit2, ...) — no sum_range, just test pairs.
• Count a between range by repeating the same column with two bounds.
• Use "<>" for non-blank and join with & for cell-based limits.
• All ranges must be the same size.

Next: averaging values across multiple conditions with AVERAGEIFS.