What is SUMIF in Google Sheets and Excel?
Quick answer
SUMIF is a spreadsheet function that adds up the numbers in one range only where a second range matches a condition, such as "add the Amount of every job whose Status is Done". It works the same way in Google Sheets and Excel.
Last updated
Take the free module →Module 1 of the free Google Workspace for Work: Sheets, Gmail and Gemini course, about 15 minutes. No account needed.
Updated · By Robert Breen
Why it matters for a small business
Most small-business lists need totals by group: money by status, hours by person, sales by region. Without SUMIF, someone filters the list and adds the numbers by hand every week, and one missed row makes the total wrong. With SUMIF, the totals sit next to the list and update the moment a row changes.
The syntax is =SUMIF(range, criterion, sum_range). The range is where the condition is checked, the criterion is what to match (a value or a cell holding it), and the sum_range is what to add. Put dollar signs on the ranges ($D$2:$D$100) so you can copy the formula down without them moving. Need two conditions at once, such as status and crew? That is SUMIFS.
SUMIF totals from the job tracker lesson
| Status (G) | Formula in H | Total |
|---|---|---|
| Booked | =SUMIF($D$2:$D$100, G2, $E$2:$E$100) | $695 |
| Done | =SUMIF($D$2:$D$100, G3, $E$2:$E$100) | $435 |
| Invoiced | =SUMIF($D$2:$D$100, G4, $E$2:$E$100) | $620 |
| Paid | =SUMIF($D$2:$D$100, G5, $E$2:$E$100) | $610 |
Eight made-up cleaning jobs that add up to $2,360.
In a real lesson: Google Sheets: Build a Job Tracker That Totals Itself
In the free Google Sheets job tracker lesson, Maria (made up) runs a six-person cleaning company and cannot see which jobs are done but not billed. Her sheet has Date, Client, Crew, Status and Amount for eight jobs.
You type the four statuses in G2 to G5 and one SUMIF in H2, then copy it down. The Done row reads $435: Greene Law $275 plus Rivera Home $160, finished work nobody has invoiced yet. Because Status is a drop-down, the text always matches and the totals never miss a row.
Try this lesson free or read the step-by-step guide.
Common confusions
SUMIF vs SUMIFS
SUMIF takes one condition. SUMIFS takes several, and puts the sum range first: =SUMIFS(E2:E100, D2:D100, "Done", C2:C100, "A").
SUMIF vs a filter
A filter hides rows so you can look at them. SUMIF leaves the list alone and writes the total in a cell that updates by itself.
Tips
- A total of 0 usually means the text does not match exactly ("Done " with a space) or the numbers were typed as text with a "$".
- Lock the ranges with dollar signs before you copy the formula down.
Related terms
More Data, Sheets and Gmail terms
Where you use it: free lessons
- Google Sheets: Build a Job Tracker That Totals Itself (Google Sheets, 15 min)
Frequently asked questions
- How do I write a SUMIF formula?
- Type =SUMIF(range, criterion, sum_range). For example, =SUMIF(D2:D100, "Done", E2:E100) adds every Amount in column E whose Status in column D is Done.
- Why does my SUMIF return 0?
- Usually the criterion does not match the cells exactly, for example an extra space or different spelling, or the numbers are stored as text. A drop-down list for the condition column prevents the first problem.
- Is SUMIF the same in Excel and Google Sheets?
- Yes. The function name, the order of the arguments and the result are the same in both.