Hands-On Excel

Hands-On Excel Challenges SUMIFS

Excel SUMIFS practice

=SUMIFS(C2:C13, A2:A13, E2, B2:B13, F2) adds the sales in column C where column A matches E2 and column B matches F2. The range to add always comes first.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

09 · SUMIFS · Core

Add up with two conditions

In G2, add up the sales for the region in E2 and the product in F2. Then change E2 or F2: your total should follow.

SUMIFS(what to add, where to check 1, condition 1, where to check 2, condition 2). The range to add comes first.

sales.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · SUMIFS

Syntax

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

Read it as: add these, where this column matches this, and that column matches that. After the first argument, the rest come in pairs: a range, then what to look for in it. A row is added only when every pair matches.

SUMIF and SUMIFS put things in a different order

FunctionWhere the range to add goes
=SUMIF(A2:A13, "North", C2:C13)last
=SUMIFS(C2:C13, A2:A13, "North")first

That swap is the most common SUMIFS error. Many people use SUMIFS even for one condition, so there's only one order to remember.

Put the conditions in cells

Typing "North" into the formula works, but then the formula only ever answers one question. Point at a cell instead (E2) and anyone can change the region without touching the formula. For comparisons with a cell, join them: ">="&G1.

Common mistakes

FAQ

How do I use SUMIFS with multiple criteria?
Start with the range to add, then add a range and a condition for each criterion: =SUMIFS(C2:C13,A2:A13,"North",B2:B13,"Laptop").
What is the difference between SUMIF and SUMIFS?
SUMIF checks one condition and takes the range to add last. SUMIFS checks one or more conditions and takes the range to add first.
Why does my SUMIFS return 0?
No row matches every condition. Check the spelling of each condition, extra spaces in the data, and that each range points at the right column.
Can SUMIFS add values that match one condition or another?
Not in one call: all conditions must match. Add two SUMIFS: =SUMIFS(C2:C13,A2:A13,"North")+SUMIFS(C2:C13,A2:A13,"South").

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Percent of a totalPercentages Starter · 3 min
  6. 06 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  7. 07 Pass or fail?IF Core · 3 min
  8. 08 Count the ones that matchCOUNTIF Core · 3 min
  9. 09 Add up with two conditionsSUMIFS Core · 3 min
  10. 10 Look it upVLOOKUP Core · 3 min
  11. 11 Find the right bracketVLOOKUP approximate match Core · 3 min
  12. 12 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  13. 13 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  14. 14 Catch the error, not the bugIFERROR Core · 3 min
  15. 15 Split first and last namesLEFT, MID + FIND Core · 3 min
  16. 16 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy