Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
SUMIF vs SUMIFS: when to use each in Excel and Sheets
Topic:SUMIF & SUMIFSExcel & Google Sheets
=SUMIF(A2:A4,"Sales",B2:B4)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Dept | Amount |
| Sales | 1200 |
| HR | 800 |
| Sales | 950 |
=SUMIF(A2:A4,"Sales",B2:B4)→2150
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Use SUMIF when you have one condition: =SUMIF(range,criteria,sum_range) — e.g., =SUMIF(A2:A100,"Sales",B2:B100) sums B where A equals 'Sales'.
- 2Use SUMIFS when you need multiple conditions: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2) — note the sum_range comes FIRST in SUMIFS.
- 3Add more condition pairs to SUMIFS as needed — you can have up to 127 condition pairs in Excel.
- 4Both functions support wildcards: use * for any characters and ? for one character in text criteria.
Tips
- The key gotcha: in SUMIF the sum_range is the LAST argument, but in SUMIFS it is the FIRST argument — mixing these up is the most common source of errors.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can I always use SUMIFS instead of SUMIF?
Yes — SUMIFS with one condition pair works exactly like SUMIF and is slightly more consistent. Many users prefer SUMIFS for everything to avoid remembering the argument order difference.
How do I use SUMIF with a date range condition?
Use SUMIFS for date ranges: =SUMIFS(C2:C100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<="&DATE(2024,12,31)) to sum between two dates.
More on SUMIF & SUMIFS
See all →Reference
SUMIF
Add up the cells in a range that meet a single condition.
How-toHow to use SUMIF with a date range
Use SUMIF with a date comparison criterion to sum values only for dates on or after (or before) a specific date in your data range.
Error fixFix 0
SUMIF returns 0 instead of the expected total in Excel and Google Sheets — fix number-as-text, criteria mismatch, range size errors, and data-type issues.
ReferenceSUMIFS
Sum values that meet multiple conditions at once.
How-toHow to use SUMIFS between two dates
Use SUMIFS with two date criteria on the same date column to sum values that fall within a specific start and end date range.
Error fixFix SUMIF
SUMIF not working in Excel and Google Sheets — diagnose formula errors, criteria syntax problems, range mismatches, and wildcard issues.