SUMIFS in Excel: Sum With Multiple Criteria (Plus SUMIF and Google Sheets)
SUMIF adds numbers that meet one condition. SUMIFS adds numbers that meet several. Here are the formulas, the argument order that trips people up, and fixes for common errors.
Updated 18 Nov 2026 · By Softotech Solutions
SUMIF vs SUMIFS syntax
=SUMIF(range, criteria, [sum_range]) tests one range and sums a second range. =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) puts the sum range first. This order difference is the most common reason people get wrong results. All ranges in SUMIFS must be the same size.
SUMIF examples
Assume region is in B2:B20 and amount is in C2:C20. =SUMIF(B2:B20,"East",C2:C20) adds the amounts for East. =SUMIF(C2:C20,">1000") adds all amounts above 1,000 (the sum range is optional when it is the same as the test range). =SUMIF(B2:B20,"<>East",C2:C20) adds everything except East.
SUMIFS examples with multiple criteria
With status in D2:D20 and date in E2:E20:
Two conditions: =SUMIFS(C2:C20,B2:B20,"East",D2:D20,"Paid") adds paid East amounts.
Number threshold: =SUMIFS(C2:C20,B2:B20,"East",C2:C20,">=1000") adds East amounts of at least 1,000.
Date range: =SUMIFS(C2:C20,E2:E20,">="&DATE(2026,1,1),E2:E20,"<="&DATE(2026,3,31)) totals the first quarter of 2026.
Cell references: =SUMIFS(C2:C20,B2:B20,G2,D2:D20,H2) lets users change the filters in G2 and H2.
Wildcards and OR logic
Use * for any characters and ? for one character: =SUMIF(A2:A20,"*shipping*",C2:C20) adds every row whose description contains the word shipping. SUMIFS combines conditions with AND. For OR, add two SUMIFS or use an array constant: =SUM(SUMIFS(C2:C20,B2:B20,{"East","West"})) totals East plus West.
SUMIF in Google Sheets
SUMIF and SUMIFS work the same way in Google Sheets: =SUMIF(B2:B20,"East",C2:C20) and =SUMIFS(C2:C20,B2:B20,"East",D2:D20,"Paid"). The argument order is identical to Excel, so formulas usually copy across without changes.
Fix common SUMIFS errors
Result is 0. The numbers may be stored as text, or the criteria text has a trailing space. Clean with TRIM or convert text to numbers. #VALUE! The ranges are different sizes. Wrong total after copying down. Lock the ranges with dollar signs, for example $C$2:$C$20. See absolute references in Excel. Dates not matching. Use ">="&DATE() rather than typed text dates.
To count rows instead of adding values, see COUNTIFS in Excel. For simple SUMIF basics read SUMIF in Excel.
Worked example with a sales table
Take a sales list with 120 rows: salesperson in column A, region in column B, product in column C, amount in column D and sale date in column E. The following formulas build a small report.
| Report line | Formula |
|---|---|
| Total sales for Maria | =SUMIFS(D2:D121,A2:A121,"Maria") |
| East region, product Widgets | =SUMIFS(D2:D121,B2:B121,"East",C2:C121,"Widgets") |
| Sales over 500 only | =SUMIFS(D2:D121,D2:D121,">500") |
| Everything except Maria | =SUMIFS(D2:D121,A2:A121,"<>Maria") |
| Sales in March 2026 | =SUMIFS(D2:D121,E2:E121,">="&DATE(2026,3,1),E2:E121,"<"&DATE(2026,4,1)) |
For the month filter use “less than the first day of the next month” instead of “less than or equal to the last day”. It avoids errors when your dates include a time of day.
Build a monthly summary
Put the first day of each month in a column, for example 1 Jan 2026 in F2 and 1 Feb 2026 in F3. Then use =SUMIFS($D$2:$D$121,$E$2:$E$121,">="&F2,$E$2:$E$121,"<"&EDATE(F2,1)) and fill it down. EDATE returns the same day one month later, so each row sums exactly one month. The dollar signs lock the ranges so they do not slide when you copy the formula. If you need a refresher, read absolute references in Excel.
Dynamic criteria with drop-downs
Create a drop-down list of regions with Data Validation, then point the formula at that cell: =SUMIFS(D2:D121,B2:B121,H1). When someone picks a different region in H1 the total updates immediately. You can add a second drop-down for the product and a third for the year to build a simple interactive report without macros.
SUMIFS, pivot tables and SUMPRODUCT
SUMIFS is best for fixed reports and dashboards where you want a formula in a specific cell. A pivot table is faster when you want to explore the data by many fields. SUMPRODUCT handles cases SUMIFS cannot, such as multiplying two columns before adding them: =SUMPRODUCT((B2:B121="East")*(D2:D121)*(F2:F121)) returns the total of amount times quantity for East rows. Use whichever is simpler to audit, and double-check one result by filtering the data and reading the total in the status bar.
Criteria syntax cheat sheet
Most SUMIFS mistakes come from the criteria text. Use this list as a reference.
| You want | Criteria to use |
|---|---|
| Equal to a word | "East" or a cell reference such as H1 |
| Not equal to a word | "<>East" |
| Greater than a number | ">500" |
| Greater than a value in a cell | ">"&H1 |
| Between two numbers | Two criteria on the same range: ">=500" and "<=1000" |
| Contains a word | "*word*" |
| Starts with letters | "INV*" |
| Blank cells | "" (empty string) |
| Not blank | "<>" |
Remember that text criteria are not case-sensitive, and that a criteria range must have exactly the same number of rows as the sum range. When two ranges are on different sheets, the formula still works as long as the sizes match. If a total looks too small, check for rows with typos in the criteria column, such as “Eest” instead of “East”, using a filter on that column.
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF uses one condition. SUMIFS uses several conditions, and its sum range comes first in the formula.
Can SUMIFS use OR logic?
Not directly. Add two SUMIFS results, or use =SUM(SUMIFS(sum_range,criteria_range,{“A”,”B”})).
How do I sum between two dates in Excel?
Use =SUMIFS(sum_range,date_range,”>=”&start,date_range,”<="&end).
Does SUMIF work in Google Sheets?
Yes. SUMIF and SUMIFS use the same arguments in Google Sheets.
Why does SUMIFS return 0?
Usually the values are text, not numbers, or the criteria contain hidden spaces.
Official reference: SUMIFS function (Microsoft Support)
Need Excel work done for you?
Talk to usMore free tools: SUMIF in Excel · COUNTIFS in Excel · Absolute references · All free calculators
Guides: How to Create a Pivot Table in Excel · How to Use the AND and OR Functions in Excel · How to Use the TEXT Function in Excel