SUMIF
SUMIF is an Excel and Google Sheets function that adds up only the cells that meet one condition, like one store's sales: =SUMIF(range, criteria, sum_range).
Select any words here to ask your Maestro about them.
You total Austin's sales with =SUMIF(A2:A9, "Austin", B2:B9). In one row, Austin was typed with a space at the end. Does SUMIF count it?
How it works: =SUMIF(range, criteria, [sum_range]). Excel checks each cell in the range against the criteria and adds the matching row from the sum range. Criteria can be text ("Austin"), a comparison (">100") or a wildcard ("Aus*"). Same in Google Sheets.
SUMIF or SUMIFS: for two conditions or more, use SUMIFS. To see every store's total at once, a pivot table is faster.
Where it breaks: SUMIF never checks that the two ranges line up. It starts at the sum range's first cell and counts down as many rows as the range has. Start the sum range one row off, like B3:B10 next to A2:A9, and every sale comes from the wrong row, with no warning. If the total doesn't match another report, run a reconciliation.
How it's used
SUMIF and SUMIFS
Three ways to use it
- =SUMIF(A2:A9, "Austin", B2:B9)Where to look, what to look for, what to add.
- =SUMIF(B2:B9, ">100")With no third range, it adds the same cells it checks.
- marked: =SUMIFS(B2:B9, A2:A9, "Austin", C2:C9, "March")For several conditions. Watch out: here the range to add comes first.
When to look closer
A target in another cell
Sales above target
- The target, $100, is in E1
- marked: =SUMIF(B2:B9, ">E1")
You want to add the sales above the target in E1, so you write =SUMIF(B2:B9, ">E1"). What does it return?
Ask your Maestro about SUMIF
It answers from the free course page closest to this word, the way it answers while you study.
You: What does "SUMIF" mean, in plain words?
Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which total at work do you add up by hand, category by category?
Asking about: Answers from the page "What if my job uses Google Drive?"
Only this browser keeps what you ask here. How it works
Learn more
Related terms
- Pivot table
A pivot table summarizes a long table in seconds: it groups by the columns you pick and sums, counts or averages another, with no formulas.
- XLOOKUP
XLOOKUP looks for a value in one column and returns the data from the same row in another column, to the right or the left: =XLOOKUP(E2,B2:B9,C2:C9).
- VLOOKUP
VLOOKUP looks for a value in a table's first column and returns the data from another column in that row: =VLOOKUP(E2,A2:C9,3,FALSE).
- Reconciliation
Comparing a total you computed with the same total from an independent source, such as a bank statement or a register report, and explaining any difference.