How to Use COUNTIFS and AVERAGEIFS in Excel: Count and Average with Multiple Criteria

If you already know SUMIFS, you are two-thirds of the way to knowing COUNTIFS and AVERAGEIFS, they are its siblings, and they speak exactly the same criteria language. SUMIFS totals the rows that match your conditions. COUNTIFS counts them. AVERAGEIFS averages them. Learn the shared grammar once and all three fall into place.

This guide takes you from a single condition to several at once, shows you the one argument-order quirk that trips up nearly every beginner, and ends on a genuinely elegant idea: an average is just a total shared out over a count, which is why AVERAGEIFS is really SUMIFS ÷ COUNTIFS wrapped in a single function. We will build everything on one small, memorable dataset so you can rebuild each formula yourself.

FAQ — What does this article answer?

Q: What is the difference between COUNTIFS and AVERAGEIFS?

A: COUNTIFS counts how many rows meet your conditions, it returns a whole number and needs no value column. AVERAGEIFS averages the numbers in a chosen column for the rows that meet your conditions. Same criteria, different question: "how many?" versus "what is the average?".

Q: What is the difference between COUNTIF and COUNTIFS (and AVERAGEIF and AVERAGEIFS)?

A: The singular versions (COUNTIF, AVERAGEIF) allow only one condition. The plural "IFS" versions allow many conditions at once, joined with AND. Since the plural versions do everything the singular ones do, most people just use COUNTIFS and AVERAGEIFS for everything.

Q: How do I count or average with more than one condition?

A: Add more criteria pairs. =COUNTIFS(range1, criteria1, range2, criteria2, ...). Every pair must be satisfied for a row to count; the AND is built in. AVERAGEIFS works the same way, but its first argument is the column of numbers to average.

Q: Why does AVERAGEIFS give a #DIV/0! error?

A: Because no rows matched your criteria, so there is nothing to average and dividing by zero rows is undefined. COUNTIFS would simply return 0 in the same situation. Wrap AVERAGEIFS in IFERROR to show a friendly message instead.

Q: Can I use greater-than, wildcards, or a cell's value as a criterion?

A: Yes. Criteria are written as text: ">50", "<>Berlin", "M*" (wildcard), or ">="&C1 to pull a value from a cell. The same criteria work in COUNTIFS, AVERAGEIFS and SUMIFS.

Q: Should I use these or a PivotTable?

A: For a handful of specific figures on a dashboard, these functions are perfect and update live. For exploring lots of groupings interactively, a PivotTable is faster. They complement each other.

One criteria language, three jobs

COUNTIFS, SUMIFS and AVERAGEIFS are all part of the same family. Give them the same conditions and they will select the same rows; they simply report different numbers about them.

Count, total, mean. Notice already that the three Berlin numbers are related — 228 ÷ 4 = 57 — and hold that thought, because it is the neat idea we finish on.

The dataset: a weekend 10K race

Everything below uses one small results table. Columns A to D, data in rows 2 to 8:

Two text columns to group by, one number column (finish time in minutes) to count and average. Perfect.

COUNTIFS: counting rows that meet conditions

The syntax

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

You give it pairs: a range to look in, and the criterion it must meet. There is no value column, COUNTIFS only ever counts rows. Every range must be the same height.

One condition

How many runners came from Berlin?

=COUNTIFS(B2:B8, "Berlin")

Excel walks the City column and tallies the matches: Anna, Cara, Eva, Gwen — that is 4.

Two conditions: the AND is automatic

How many runners were from Berlin and in the Senior category?

=COUNTIFS(B2:B8, "Berlin", C2:C8, "Senior")

Think of each criterion as a gate. A row is counted only if it clears every gate, that is what "AND" means here. Cara and Gwen are the only two who are both in Berlin and Senior, so the answer is 2. Add a third pair for a third gate, and so on.

Numbers, not just text

Criteria can compare numbers. How many runners finished in under 50 minutes?

=COUNTIFS(D2:D8, "<50")

Dan (49) and Finn (47), that is 2. The comparison lives inside the quotation marks: "<50", not <50.

Between two numbers: the same range twice

Here is a small elegance: to count values in a range, use the same column twice, once with a lower bound and once with an upper bound. How many runners finished between 50 and 60 minutes inclusive?

=COUNTIFS(D2:D8, ">=50", D2:D8, "<=60")

Anna (52), Cara (58) and Eva (55) qualify 3. COUNTIFS is perfectly happy to test the same column against two conditions.

A wildcard

Text criteria can use * (any run of characters) and ? (exactly one character). How many runners came from a city starting with "M"?

=COUNTIFS(B2:B8, "M*")

München appears twice 2.

AVERAGEIFS: averaging the rows that match

AVERAGEIFS answers "what is the average of these numbers, for the rows that meet my conditions?".

The syntax: mind the order

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Read that first argument carefully, because it is where nearly everyone slips.

The plural "IFS" functions put the range to crunch first: SUMIFS starts with the sum range, AVERAGEIFS starts with the average range, and then come the criteria pairs. (COUNTIFS is the exception only because it has no value range at all.) The trap is that the singular cousins do the opposite, AVERAGEIF(range, criteria, [average_range]) puts that range last. Same idea, opposite order. When an AVERAGEIFS won't behave, check this first.

One condition

Average finish time for Berlin runners:

=AVERAGEIFS(D2:D8, B2:B8, "Berlin")

The four Berlin times are 52, 58, 55 and 63, which average to 57 minutes.

Two conditions

Average finish time for Berlin Seniors:

=AVERAGEIFS(D2:D8, B2:B8, "Berlin", C2:C8, "Senior")

Just Cara (58) and Gwen (63): an average of 60.5 minutes. As with COUNTIFS, every criterion must be met the same built-in AND.

The elegant part: AVERAGEIFS = SUMIFS ÷ COUNTIFS

An average is nothing mysterious, it is a total shared out over a count. So the Berlin average could be built by hand from its two siblings:

=SUMIFS(D2:D8, B2:B8, "Berlin") / COUNTIFS(B2:B8, "Berlin")

That is 228 / 4 = 57, exactly what AVERAGEIFS returned. AVERAGEIFS simply does both steps for you in one cell.

This picture also explains AVERAGEIFS's most confusing error. If no rows match, the count underneath is 0, and you cannot share a total over zero rows. So AVERAGEIFS returns #DIV/0!, while COUNTIFS, having nothing to divide, calmly returns 0. That difference is not a bug; it falls straight out of what an average is.

Guarding against #DIV/0!

When a criterion might match nothing (say, a city with no runners), wrap the formula so it fails gracefully:

=IFERROR(AVERAGEIFS(D2:D8, B2:B8, "Köln"), "no runners")

Now an empty result shows "no runners" instead of an error. (For more on tidying up errors, see the IFERROR / IFNA guide linked below.)

Your criteria cookbook

Every criterion below works identically in COUNTIFS, AVERAGEIFS and SUMIFS. Keep this card handy.

Two details worth committing to memory. First, the comparison or wildcard always lives inside the quotes: ">50", "M*". Second, to compare against a value that sits in a cell, join the operator to the cell reference with &: ">="&C1. That last trick lets a criterion follow a dropdown or an input cell without editing the formula, handy for interactive dashboards, and it leans on the same string-building idea covered in the cell-references guide.

Common mistakes and how to fix them

Ranges of different heights. Every criteria_range and the average_range must span exactly the same rows. Mismatched heights give a #VALUE! error. An Excel Table (below) removes the problem entirely.

Operators outside the quotes. ">50" is a text criterion; writing >50 bare, or ">"50, will not work. Keep the whole condition inside one pair of quotes.

Forgetting the value range in AVERAGEIFS. If AVERAGEIFS returns something baffling, you probably started with a criteria range instead of the average range. The range to average comes first.

Expecting AVERAGEIFS to ignore text. It averages only the matching numbers, but the average_range should contain numbers; blank cells are skipped, text cells can cause surprises. Keep that column clean.

Building an OR by mistake. Criteria pairs are always AND. To count "Berlin or München", add two COUNTIFS together, or reach for SUMPRODUCT (linked below), which can express OR in a single cell.

Make it robust with Excel Tables

Hard-coded ranges like B2:B8 go stale the moment a new runner is added. Put the data in a Table (select it and press Ctrl+T) and use its column names:

=COUNTIFS(Race[City], "Berlin", Race[Category], "Senior")
=AVERAGEIFS(Race[Minutes], Race[City], "Berlin")

Now Race[City] always means the whole City column, however many rows the Table grows to. New results are included automatically and the ranges can never fall out of step.

When to use which

Use COUNTIFS whenever the question is "how many rows match?". Use AVERAGEIFS for "what's the average of a number, for the rows that match?". Use SUMIFS for the total. All three share one criteria language, so the moment you can write one, you can write all three.

Reach past them to SUMPRODUCT when you need OR logic, or a condition based on a calculation, in a single cell. And reach for a PivotTable when you want to explore many groupings at once rather than pin down a few specific figures.

Summary

Learn the criteria language once and three functions come free. COUNTIFS counts, SUMIFS totals, AVERAGEIFS averages and that last one is only ever the first two, quietly divided.

What to read next

If you have not met the workhorse of the family yet, start with SUMIFS. The errors guide covers the IFERROR wrapper that tames #DIV/0!. The Tables guide keeps your ranges from going stale, and the SUMPRODUCT guide is where to go when you need OR logic or a calculated condition that COUNTIFS can't express.

See: How to Use SUMIFS to Sum with Multiple Criteria in Excel

See: How to handle errors with IFERROR and IFNA

See: Excel Tables: why every dataset should be one

See: How to Use SUMPRODUCT: Multiply, Sum, and Count with Conditions