The idea
A conditional function is a loop you never write. SUMIF goes down a column, asks of each row "does this row pass my test?", and adds up the third column only for the rows that said yes. COUNTIF does the same and counts the yeses.
Think of it as a filter followed by an aggregation, the same thing SQL spells WHERE and SUM. The test is the part you control, and almost every mistake in this chapter is a mistake in the test. Read the picture below as a short script: pick a test, mark the rows that pass, then add up only the marked rows.
Figure 1
Eight Black Friday orders, filtered by SUMIFS
| order_id | channel | status | coupon_code | subtotal |
|---|---|---|---|---|
| 12059 | app | refunded | BFCM25 | 424.46 |
| 12060 | app | completed | 119.94 | |
| 12061 | web | completed | BFCM25 | 8.99 |
| 12062 | web | completed | 339.86 | |
| 12063 | app | completed | 132.97 | |
| 12064 | app | completed | 2002.88 | |
| 12065 | app | completed | 136.95 | |
| 12066 | app | completed | BFCM25 | 26.99 |
| formula | returns |
|---|---|
| =SUMIF(B2:B9,"app",E2:E9) | 2844.19 |
| =SUMIFS(E2:E9,B2:B9,"app",C2:C9,"completed") | 2419.73 |
- A day of Harbor & Pine orders in
A2:E9. The question: how much revenue came through the app? The answer is a sum ofsubtotal, but only over some rows. SUMIF(B2:B9, "app", E2:E9)has three parts. The first range is tested, row by row, against the criterion "app". The third range is what gets added. Rows 12061 and 12062 fail the test.- Only the surviving rows contribute their
subtotal: 424.46 + 119.94 + 132.97 + 2002.88 + 136.95 + 26.99 = 2844.19. - Order 12059 was refunded, so that revenue is not real.
SUMIFSadds a second test, and a row must pass both. 12059 fails the status test and drops out. - Five rows pass both tests: 2844.19 − 424.46 = 2419.73. Every extra condition can only remove rows, never add them.
Why it exists
Managers rarely ask for "the total". They ask for "the total for the app", "the total for completed orders over $250", "the promo weekend only". Without conditional functions you would filter the sheet, select the visible cells, read a number off the status bar and paste it somewhere, and you would have to redo all of it when one order changes.
A conditional formula keeps the question live. Point the criterion at a cell holding "app", and typing "web" in that cell re-answers the question in an instant. One formula dragged across and down a grid of channels and statuses gives the whole summary table.
How it works under the hood
Ranges line up row for row
In SUMIF(range, criterion, sum_range) the range being tested and the range being summed must have the same shape. Excel pairs them by position: row 1 of one with row 1 of the other. Shift one of them by a row, for example B2:B9 against E3:E10, and every test is applied to the wrong amount. No error appears. SUMIFS reverses the order of its arguments: the range to add comes first, then pairs of range and criterion.
Many conditions mean "and"
SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS accept as many range-and-criterion pairs as you like. A row counts only if every pair passes. For "or", add two separate SUMIFS, but make sure no row can pass both or you will count it twice. MAXIFS and MINIFS find the largest or smallest value among the rows that pass, such as the biggest completed order of a given weekend.
Criteria are strings
Every criterion is read as text and interpreted. A plain value means equals. An operator at the start (>, >=, <, <=, <>) changes the comparison. * and ? are wildcards in text. To compare with a cell, glue the operator and the cell together: ">="&H1 becomes ">=250" when H1 holds 250. Forgetting the quotes or the & is the usual reason a criterion silently matches nothing.
Figure 2
What each criterion matches in the same eight orders
| criterion | tested_against | rows_matching |
|---|---|---|
| "app" | channel | 6 |
| ">=250" | subtotal | 3 |
| "<>refunded" | status | 7 |
| "BF*" | coupon_code | 3 |
| "<>BFCM25" | coupon_code | 5 |
| "" | coupon_code | 5 |
- A criterion is a small string. A bare word means equals:
"app"matches the six rows whose channel is exactly app (case does not matter). - A comparison operator goes inside the quotes.
">=250"keeps subtotals of 250 or more: 424.46, 339.86 and 2002.88, so three rows. "<>refunded"means not equal. Seven of the eight rows pass, as only 12059 is refunded.*stands for any run of characters and?for one."BF*"matches coupon codes that start with BF: three rows. Blank cells are not text, so a wildcard never matches them."<>BFCM25"matches the five rows with a different coupon, and the blank cells count as different. If you only want rows that have a coupon, use"*", not"<>".
What counts as blank
There are three questions about emptiness, and each has its own function. COUNTA counts cells that hold anything, COUNT counts only numbers, and COUNTBLANK counts empty cells. A cell holding an empty string "" returned by a formula looks blank but is text. Because "<>x" means "anything except x", it includes blanks. If you mean "has a value other than x", say so with two conditions: "<>x" and "<>".
One formula for a whole summary table
The sharpest use of these functions is a grid. Put the channels down column A and the statuses across row 1, then write one SUMIFS in the top-left cell: the amounts range and both criteria ranges locked with $ on every corner, and the two criteria pointing at the labels with mixed references, $A2 for the channel and B$1 for the status. Fill it right and down and every cell asks its own question: this channel, this status. It is the mixed-reference idea from the first chapter doing real work, and it replaces a pivot table when you need a small, fixed layout.
Add a total row and column with plain SUM, then compare the grand total with a SUM of the whole amount column. If they differ, some order has a channel or a status that your labels do not list, a typo or a trailing space, and that order is silently missing from the table.
Checking the answer
The quick check is a filter. Apply an AutoFilter with the same conditions, select the amount column and read the sum from the status bar at the bottom. If it matches the formula, the criteria are doing what you think. If not, compare the count of visible rows with COUNTIFS using the same conditions: a count that disagrees points to a bad criterion; a count that agrees but a sum that doesn't points to misaligned ranges.
Common misconceptions
"SUMIF adds up the column I test"
The first range is tested and the third is added. They are often different columns, and if you leave out the third, SUMIF adds the tested range itself. That is only what you want when the test and the amount are the same column, such as "add up the subtotals that are at least 250".
"A second condition widens the result"
Each extra pair in SUMIFS narrows it. Two tests joined this way mean both must be true. If you want orders from the app or the web, a single SUMIFS with both channel conditions returns zero, because no row is in two channels.
"<> means has a value that is different"
It means not equal to, and an empty cell is not equal to anything. Counting "orders without the BFCM25 coupon" with "<>BFCM25" therefore includes orders that have no coupon at all. That happens to be the right answer there, but for "orders with some other coupon" it silently overcounts.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. A blank cell is not equal to BFCM25, so it passes <>BFCM25. Orders without any coupon are counted.Try another answer.
Right. Six orders are from the app. 12059 and 12066 use BFCM25 and fail the second test; the other four have a blank coupon, which counts as different.
Not quite. Blanks are not ignored. They are compared like any other value, and a blank is not equal to BFCM25.Try another answer.
Not quite. That is the count for the coupon test alone. The first condition restricts the rows to the app first, and both must pass.Try another answer.
Lesson done: it is ticked on the map.