SUMIFS and COUNTIFS: the GROUP BY of Excel

In SQL you write one query and get every group back at once:

select city, sum(fare)
from trips
where status = 'completed'
group by city;

Excel inverts this. There is no statement that returns all groups — you write down the list of groups yourself, one per row, and then ask for each group's number individually. SUMIFS is that individual ask.

=SUMIFS(trips[fare], trips[status], "completed", trips[city], A2)

Read it as: sum this column, where this column equals this, and this column equals this. The WHERE clause and the GROUP BY key have been mashed into one argument list. Everything after the first argument is a pair — range, then criterion — and you can have up to 127 of them.

The plural matters

SUMIF and SUMIFS are different functions with different argument orders, which is the single most common source of silently wrong numbers in this whole category.

The sum range moves from last to first. Copy a working SUMIF and add an S plus another condition, and you get a formula that runs, returns a number, and is wrong. It does not error. Nothing turns red.

Just always use the plural form, even for a single condition. One habit, one argument order, and the second condition costs two more arguments instead of a rewrite. COUNTIFS and AVERAGEIFS follow the SUMIFS convention, so this also makes the family consistent — except COUNTIFS, which has no value range at all since it counts rows rather than summing a column.

Criteria are strings, and that is stranger than it sounds

In SQL a predicate is part of the language. In Excel a criterion is text that Excel parses at evaluation time. This is fine for equality:

=COUNTIFS(orders[status], "delivered")

It gets strange the moment you want a comparison, because the operator lives inside the quotes:

=COUNTIFS(scores[value], ">=80")

And it gets genuinely trappy when the threshold is not a literal. You cannot write ">=B1" — that compares against the four-character text "B1", matches nothing, and returns zero. The operator and the value have to be concatenated:

=COUNTIFS(scores[value], ">="&B1)

A criteria formula that returns 0 instead of erroring is nearly always a missing &. Zero is a legitimate answer, so nothing warns you. This is the Excel equivalent of a SQL predicate that compares a column to the string 'B1' instead of a value — except SQL would usually complain about the type, and Excel never will.

Other criteria forms worth knowing:

• "<>" on its own means not blank. "<>delivered" means not equal to that text.
• "*" and "?" are wildcards inside criteria — "KL*" matches anything starting with KL. There is no LIKE; this is it.
• A blank cell as a criterion matches everything, which is why a formula referencing an empty parameter cell quietly returns the grand total instead of a filtered one.

HAVING has no equivalent, and this catches people out

HAVING filters after aggregation — "cities with more than 500 completed trips". Excel has no such stage, because there is no aggregation stage to come after. You compute the per-city number in a column, and then filter that column as data: sort it, apply an autofilter, or wrap the whole thing in an IF.

This is worth saying out loud in an interview. "In SQL I'd use HAVING here; in Excel I'd compute the count per city in column B and then filter column B" demonstrates you understand what the stage actually is, rather than hunting for a function that does not exist.

The four traps

Mismatched range heights. SUMIFS(B2:B5000, C2:C4999, "x") — the sum range and the criteria range are different lengths. Modern Excel throws #VALUE!. Older behaviour was worse: it aligned them from the top and silently used the wrong rows. Use whole-column references or a proper Table so the ranges cannot drift.

Text that looks numeric. A column exported from a warehouse often arrives as text — "1200" rather than 1200. SUMIFS with a numeric criterion matches none of it and returns 0. The tell is that the values are left-aligned by default rather than right-aligned. Same class of problem as comparing a varchar column to an integer in SQL.

Trailing whitespace on the group key. "Kuala Lumpur " and "Kuala Lumpur" are different criteria, so one city splits into two rows of your summary and both are wrong. TRIM the key column before you aggregate on it. In SQL this bites you in GROUP BY in exactly the same way.

Double counting on one-row-per-line data. Marketplace exports are usually one row per order line, not per order. SUMIFS over an order-level column — shipping fee, order total — adds it once per line. The correct answer needs a deduplication step first, and this is the trap most often planted deliberately in marketplace interview questions.

The practical shape of an answer

Given a question like "revenue by region for completed orders last month", a strong answer looks like this:

1. List the distinct regions down a column — by hand if there are six, or with UNIQUE if the version supports it. 2. One SUMIFS beside the first region, with every condition in it, and the region reference relative so it fills down. 3. Fill down. 4. Total the column and check it against a SUMIFS with the region condition removed. If those two disagree, a region is missing or a key is dirty.

Step 4 is the one that separates candidates. It is ten seconds of work and it catches whitespace mismatches, missing groups and double counting in a single check.

Next

The other half of nearly every Excel interview question is the lookup — combining two tabs before you can aggregate at all. VLOOKUP vs INDEX+MATCH vs XLOOKUP covers which one to reach for, and why the most common default is the wrong one.

For the SQL side of the same interview, aggregation questions in the bank are graded in the browser, and the GROUP BY chapter starts from scratch.