Math formula
SUMIFS in Excel and Google Sheets
Add up numbers that meet one or more conditions.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- sum_range
- The numbers to add.
- criteria_range1
- The column to test.
- criteria1
- The condition, e.g. "Marketing" or ">1000".
Example
| Row | A | B | C |
|---|---|---|---|
| 1 | Dept | Month | Cost |
| 2 | Marketing | Jan | 500 |
| 3 | Sales | Jan | 300 |
| 4 | Marketing | Feb | 700 |
| 5 | |||
| 6 | Marketing total |
C6 =SUMIFS(C2:C4,A2:A4,"Marketing")
Result: 1200
Adds every cost where the department is Marketing.
Ready-to-copy formulas
Sum by category
=SUMIFS(C:C,A:A,"Marketing")Sum between two dates
=SUMIFS(C:C,B:B,">="&E1,B:B,"<="&E2)Sum with two conditions
=SUMIFS(D:D,A:A,"Marketing",B:B,"Jan")
Tips
- The sum range comes first in SUMIFS, but last in SUMIF.
- Join operators and cells with &, e.g. ">"&E1.
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.