SUMIFS explained with 5 real examples

It is strange to write, but one of the most underrated formulas is SUMIF, and its bigger version, SUMIFS.

SUM is something a smart eight-year-old can learn. Now meet the cool older brother of SUM.

If you don't want to complicate things with a pivot table and just want to see quickly how your sales team performed this month, SUMIFS is the way to go. (And if you want to count instead of add, like how many students in your class reached a 3.5 GPA, its sibling COUNTIFS works exactly the same way.)

How SUMIFS works

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

1. sum_range: the numbers you want to add up (e.g. the Sales column).
2. criteria_range1: where to look (e.g. the Name column).
3. criteria1: what to match (e.g. "Anna").
4. Add more pairs of where to look + what to match for every extra condition. A row is only added if it matches all of them.

SUMIF (without the S) takes only one condition, and the order is different: =SUMIF(where to look, what to match, what to add). If you learn only one, learn SUMIFS: it handles one condition too.

1. Team performance: who sold what, and when

You track your team's sales in a sheet with monthly data. After the third month things get noisy, and you can't catch trends in a fraction of a second anymore.

Anna's total so far:

=SUMIFS(C2:C7, A2:A7, "Anna")        → 13,800

Ben in March only (two conditions):

=SUMIFS(C2:C7, A2:A7, "Ben", B2:B7, "Mar")        → 3,900

Pro move: type the rep's name in a cell (say F2) and point at it: =SUMIFS($C$2:$C$7, $A$2:$A$7, F2). Fill it down next to a list of names and you have a leaderboard that updates itself.

2. A small bakery: what actually sells

You run a small bakery and want to know your best-selling categories: pastries, milk, soft drinks.

=SUMIFS(C2:C7, B2:B7, "Pastries")        → 780

Put the three category names in E2:E4, write =SUMIFS($C$2:$C$7, $B$2:$B$7, E2) in F2 and fill down: Pastries 780, Soft drinks 340, Milk 240. That's a mini report in three cells.

3. Your own spending: how much went on coffee last month?

Do you track your spending? Then check how much you spent on coffee last month. This is where dates come in.

Coffee in September only:

=SUMIFS(C2:C7, B2:B7, "Coffee", A2:A7, ">="&DATE(2026,9,1), A2:A7, "<"&DATE(2026,10,1))        → 12.50

The date conditions are just two more pairs: on or after 1 September, and before 1 October. Note the ">="& part: the comparison goes in quotes and is glued to the date with &.

4. Travel costs: who paid for what

A weekend away with friends, one shared sheet, and the eternal question at the end: who owes whom?

=SUMIFS(C2:C7, A2:A7, "Priya")                      → 592   (everything Priya paid)
=SUMIFS(C2:C7, B2:B7, "Food")                       → 194   (the whole food bill)
=SUMIFS(C2:C7, A2:A7, "Priya", B2:B7, "Food")       → 112   (Priya's food only)

Total the trip, divide by four, and compare with each person's SUMIFS: that's your settle-up list.

5. Screen time check

Export your screen time (or just jot it down for a week) and ask the uncomfortable question: how much time goes on social apps on work days?

=SUMIFS(D2:D7, C2:C7, "Social", A2:A7, "<>Sat", A2:A7, "<>Sun")        → 140 minutes

"<>Sat" means “not Saturday”. You can use the same column twice, once for each day you want to leave out.

Common mistakes

• Ranges of different sizes. C2:C7 with A2:A10 gives an error (#VALUE! in Google Sheets). Every range must have the same number of rows.
• Mixing up SUMIF and SUMIFS. SUMIF puts what to add last, SUMIFS puts it first.
• Comparisons without quotes. Write ">100", not >100. Pointing at a cell? ">"&F1.
• Numbers stored as text. If a total looks too small, check that the amounts are real numbers (right-aligned) and not text.
• Expecting OR. SUMIFS only does AND: every condition must be true. For “Coffee OR Tea”, add two SUMIFS together.

FAQ

What's the difference between SUMIF and SUMIFS? SUMIF takes one condition, SUMIFS takes one or more, and the argument order is different. SUMIFS can do everything SUMIF does.

Does SUMIFS work the same in Excel and Google Sheets? Yes, the syntax is identical.

Can SUMIFS use wildcards? Yes: "*milk" matches Fresh milk and Chocolate milk.

How do I count instead of add? Use COUNTIFS with the same conditions, just without the sum range.

Keep going

• Practise SUMIF for free in White Belt lesson 11, with instant feedback.
• Next read: The FILTER function in Google Sheets: 7 examples and XLOOKUP vs VLOOKUP vs INDEX/MATCH.