COUNTIFS & SUMIFS: Count or Sum by Multiple Criteria

You've got a table and you need one number — a count or a total that only applies to the rows meeting a few conditions at once. Here's how to do it properly in Excel, the traps that quietly hand you the wrong answer, and the one-line way to skip the syntax entirely.

This is the most common question on Excel forums, and for good reason: "how many deals are Closed Won and in the West region?" sounds simple, but the moment you stack a second or third condition, the formula gets finicky and the answer gets fragile. Below I'll show you the real Excel way — COUNTIFS, SUMIFS, OR-logic and unique counts — the four things that silently break them, and a faster route when you'd rather just ask.

The fast way: just ask for the number

Before the formulas, the shortcut, because for a lot of people it's the whole answer. If you've got the Quiriz add-in in Excel, you don't build a nested formula — you say what you want in a cell:

=QUIRIZ.ASK("how many deals are closed won in the West region over $10,000", "short")

The "short" mode returns just the number, so it drops into a cell like any formula. It reads your actual columns, so "deals," "West" and "$10,000" land on the right fields — no ">10000" text string, no size-matched ranges, none of the traps below. Change the question in plain English instead of rebuilding the formula. That's the pitch; the rest of this guide is the honest, do-it-yourself version so you understand what's happening either way.

Count rows meeting several conditions — COUNTIFS

COUNTIFS counts the rows where every condition is true. That's AND logic. You give it one range, criteria pair per condition:

=COUNTIFS(Status, "Closed Won", Region, "West")

Add a number condition — Closed Won, in the West, over $10,000:

=COUNTIFS(Status, "Closed Won", Region, "West", Amount, ">10000")

Note the operator lives inside the quotes. To compare against a cell instead of a hard-coded number, join with &:

=COUNTIFS(Amount, ">=" & G1)

Sum instead of count — SUMIFS

Same shape, one difference: the first argument is the column you're adding up.

=SUMIFS(Amount, Status, "Closed Won", Region, "West")

Read it out loud as: sum Amount, where Status is Closed Won and Region is West. Once that sentence matches the formula, SUMIFS stops feeling fiddly.

"Either / or" conditions (OR logic)

Here's the catch that trips everyone: COUNTIFS is strictly AND. There's no OR switch. For West or East, you add two COUNTIFS together:

=COUNTIFS(Region, "West") + COUNTIFS(Region, "East")

That's fine for two values. Once it's more, SUMPRODUCT scales better — count rows where Region is West or East in one shot:

=SUMPRODUCT((Region="West") + (Region="East"))

Count unique values that meet a condition

Modern Excel (365 or 2021) makes this one clean. How many distinct customers ordered in the West:

=COUNTA(UNIQUE(FILTER(Customer, Region="West")))

FILTER keeps the matching rows, UNIQUE strips repeats, COUNTA counts what's left. On older Excel this took a small essay of a formula; now it's one line.

The four things that silently break these

None of these throw a clear error. They just give you a confidently wrong number, which is worse.

• Text that looks like a number. If Amount is stored as text — "1000" instead of 1000 — it won't match ">1000". Fix it with Data → Text to Columns, or multiply the column by 1.
• Trailing spaces. "West " is not "West", and you'll never spot it by eye. Wrap the source in TRIM, or clean it before you count.
• Mismatched range sizes. Every range inside one COUNTIFS has to be the same height, or you get #VALUE!. Easy to break when one column runs a few rows longer.
• Accidental wildcards. "*west*" matches any cell containing "west"; "?" matches a single character. Useful on purpose, a silent bug by accident.

This is exactly why the plain-English route exists. When the conditions pile up and you're chasing a quote-mark or a stray space, it's often quicker to just ask:

=QUIRIZ.ASK("total won revenue by rep for deals above $10k, this quarter", "table")

Here "table" spills the breakdown as rows. Same job as a stack of SUMIFS, without the four failure modes.

Which should you use?

Honestly, it depends on the job in front of you:

• A one-off number on a small, clean table? Write the COUNTIFS. It's right there, it's free, and you'll have it in ten seconds.
• Messy data, three-plus conditions, or a number you'll re-check as the data changes? That's where the plain-English ask saves you the debugging — and re-asking is just editing a sentence.

Both are valid. The formula teaches you what's happening; the ask gets you to the answer faster when the formula would be a fight.

Frequently asked questions

Formula syntax shown targets Excel 365 / 2021. Dynamic-array functions like FILTER and UNIQUE aren't available in older versions.