Conditional totals: COUNTIF, SUMIF, AVERAGEIF and their S versions

The idea

The café has grown and now has branches in three cities. At the end of the week the owner gets one list with every sale from all three branches mixed together. SUM can tell them the grand total, but that's rarely the question they actually have. They want to know how much Jakarta sold compared to Bandung. How many Drinks orders came in. Whether the average Food order is worth more than the average drink.

Each of those questions is a total, a count or an average, but only of some of the rows. You could sort the list and add up each group by hand, but the next week you'd have to do it all again. Instead, Excel has versions of COUNT, SUM and AVERAGE with a filter built in. You tell them which rows to include, like "only where the branch is Jakarta", and they ignore everything else.

This module's sheet is that weekly list: ten sales from three branches, in rows 2 to 11:

COUNTIF

The simplest one is COUNTIF, which answers "how many?". You give it a range to look in and the value to look for, which Excel calls the criteria, and it counts how many cells match:

# Count the Drinks orders
H1: =COUNTIF(D2:D11, "Drinks")
# Count the Jakarta orders
H2: =COUNTIF(B2:B11, "Jakarta")

H1: 6
H2: 4

Six of the ten sales were drinks, and four of the ten sales happened in Jakarta. Like the comparisons in module 6, text criteria ignore capital letters, so "drinks" would match too.

SUMIF and AVERAGEIF

Counting is useful, but the owner mostly cares about money. To add up Jakarta's revenue, Excel has to do two things at once: look in the Branch column to decide which rows count, and then add up the numbers from the Revenue column for those rows. So SUMIF takes three arguments: the range to check, the criteria, and the range to add up:

SUMIF(range to check, criteria, range to add up)

AVERAGEIF works exactly the same way, but gives you the average instead of the total:

# Add up Jakarta's revenue
I1: =SUMIF(B2:B11, "Jakarta", F2:F11)
# The average revenue of a Food order
I2: =AVERAGEIF(D2:D11, "Food", F2:F11)

I1: 697000
I2: 173333.3333

A good way to read SUMIF is out loud: "where column B says Jakarta, add up column F". Jakarta had four sales (300000, 135000, 162000 and 100000), and together they make 697000. The three Food orders average out at about 173333.

The two ranges should be the same size, because Excel pairs them up row by row: row 5 of the Branch column goes with row 5 of the Revenue column. If one range is longer than the other, the rows no longer line up and the answer quietly becomes wrong.

Writing criteria

So far the criteria has been a plain word, like "Jakarta", which means "exactly equal to this". But often you want something looser, like "10 or more items" or "anything that isn't Food". You can do that by starting the criteria with one of the comparison signs from module 6, written inside the quotes. There are also two wildcard characters for matching part of a piece of text:

# Count the orders of 10 items or more
J1: =COUNTIF(E2:E11, ">=10")
# Count the orders that aren't Food
J2: =COUNTIF(D2:D11, "<>Food")
# Add up the revenue of every product starting with Kopi
J3: =SUMIF(C2:C11, "Kopi*", F2:F11)

J1: 4
J2: 7
J3: 925000

The * in "Kopi*" means "anything can come after this", so it matches both Kopi Susu and Kopi Hitam. That's handy when products are named in a consistent way and you want all the coffees together without listing them one by one.

Criteria from a cell

Typing ">=10" straight into the formula works, but it means that if the manager wants to see orders of 8 or more instead, someone has to edit the formula. It's nicer to put the number in its own cell, so anyone can change it. To use a cell in the criteria, you keep the comparison sign in quotes and join the cell to it with & (the joining symbol, which module 8 covers in detail). Here the minimum quantity is typed in H1:

H1: 10
# Count the orders of at least the quantity typed in H1
H2: =COUNTIF(E2:E11, ">="&H1)

H1: 10
H2: 4

Several conditions: COUNTIFS, SUMIFS, AVERAGEIFS

Real questions often have more than one condition. "How much did Jakarta sell?" is one condition. "How much did Jakarta sell in drinks?" is two, and a row should only count if both are true. That's what the versions ending in S are for. They take as many pairs of "range to check, criteria" as you need, and a row only counts if it matches all of them, just like the AND from module 6:

# Count the Drinks orders in Jakarta
L1: =COUNTIFS(B2:B11, "Jakarta", D2:D11, "Drinks")
# Add up the revenue of Drinks in Jakarta
L2: =SUMIFS(F2:F11, B2:B11, "Jakarta", D2:D11, "Drinks")
# The average quantity of a Drinks order in Bandung
L3: =AVERAGEIFS(E2:E11, B2:B11, "Bandung", D2:D11, "Drinks")

L1: 3
L2: 562000
L3: 9.5

MAXIFS, MINIFS and dates

MAXIFS and MINIFS find the biggest or smallest value among the matching rows, so they answer questions like "what was our biggest Drinks order?" or "what was the smallest sale in Surabaya?". They're written the same way as SUMIFS: the range to look at comes first, then the pairs of conditions.

The other very common filter is by date, because reports are usually about a period: this week, this month, since the promotion started. Remember from module 1 that Excel stores dates as numbers, so later dates are bigger numbers. That means you can use the normal comparison signs, like ">=" for "on or after". To write the date itself, use DATE(year, month, day) and join it to the sign with &, the same way as a cell:

# The biggest Drinks order
M1: =MAXIFS(F2:F11, D2:D11, "Drinks")
# The smallest Surabaya sale
M2: =MINIFS(F2:F11, B2:B11, "Surabaya")
# Revenue from 4 March onwards
M3: =SUMIFS(F2:F11, A2:A11, ">="&DATE(2026,3,4))
# Count the orders from 3 to 4 March
M4: =COUNTIFS(A2:A11, ">="&DATE(2026,3,3), A2:A11, "<="&DATE(2026,3,4))

M1: 300000
M2: 180000
M3: 1102000
M4: 4

The biggest drinks order was the 300000 one from Jakarta, and Surabaya's smallest sale was 180000. M3 adds up everything sold from 4 March onwards.

M4 is worth a closer look, because it uses the same column twice. There's no single criteria that means "between the 3rd and the 4th", so you give two conditions on the date column: "on or after the 3rd" and "on or before the 4th". Only dates that pass both checks are counted, which gives the four sales from those two days.

A summary table

Now you can put everything together and answer the owner's very first question properly, with a small table showing each branch's revenue. You could write a separate SUMIFS for each branch, but with the absolute references from module 4, one formula can fill the whole table. The branch names go in column H, and each row looks up its own branch:

H1: Branch
H2: Jakarta
H3: Bandung
H4: Surabaya
I1: Revenue
I2:I4: =SUMIFS($F$2:$F$11, $B$2:$B$11, H2)

H1: Branch
H2: Jakarta
H3: Bandung
H4: Surabaya
I1: Revenue
I2: 697000
I3: 559000
I4: 675000

The data ranges are locked with $, because every row should search the same list of sales. The criteria H2 is left relative, so when the formula is filled down it moves to H3 and then H4, and each row picks up its own branch name. If a fourth branch opens, you just add its name under Surabaya and fill the formula one row further.

What you've built here is a hand-made summary report, and it's a very common thing to need. Module 15 shows PivotTables, which build this kind of table for you automatically.

Recap

• COUNTIF(range, criteria) counts matches; SUMIF and AVERAGEIF add or average another column where the condition matches.
• Criteria can be "text", "<>text", ">=10", wildcards "Kopi*" and "?", or ">="&cell.
• COUNTIFS, SUMIFS, AVERAGEIFS require all conditions; their sum or average range comes first.
• Lock the data ranges and keep the criteria relative to fill a summary table.