Sum with Multiple Conditions using SUMIFS in Excel and Google Sheets
Purpose
SUMIFS is the multi-condition version of SUMIF and one of the most useful functions in any analyst's toolkit. Where SUMIF…
SUMIFS is the multi-condition version of SUMIF and one of the most useful functions in any analyst's toolkit. Where SUMIF sums based on one condition, SUMIFS lets you specify multiple conditions that all must be true simultaneously before including a row in the total. Total revenue from the North region for Sales products above a minimum order value. Total salaries for Engineering employees hired after a certain date. Total expenses for a specific cost centre in a given month. These multi-dimensional summaries that would require pivot tables or helper columns become single formulas with SUMIFS. I use it daily in financial reporting and operations analysis.
Syntax
✓ Excel 2007+
✓ Google Sheets
= Same syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments
| Argument | Required | Description |
|---|---|---|
| sum_range | Required | The column containing the values to add up. Only values from rows that meet all specified conditions will be included in the total. This is always the first argument in SUMIFS, unlike SUMIF where it is the third. |
| criteria_range1 | Required | The first column to test against a condition. SUMIFS checks each row in this column against criteria1 and only includes the row in the sum if the condition is met. |
| criteria1 | Required | The condition for criteria_range1. Can be a value, text in quotes, comparison expression like >100, wildcard, or cell reference. Only rows satisfying this condition and all other conditions are summed. |
| criteria_range2, criteria2 | Optional | Additional range and condition pairs. Each pair adds another AND condition that must be satisfied. Up to 127 criteria range and criteria pairs are supported. |
How it works
SUMIFS works by evaluating every row in your data simultaneously across all the conditions you specify. For each row, it checks whether the value in criteria_range1 meets criteria1, whether the value in criteria_range2 meets criteria2, and so on for every pair. Only rows that pass every condition are included in the sum. This AND logic is what makes SUMIFS different from adding two SUMIF results together — SUMIF addition gives you OR logic while SUMIFS gives you AND logic. A crucial difference from SUMIF is the argument order: in SUMIFS the sum_range comes first, before the criteria. In SUMIF the sum_range comes last. This trips up many users who know SUMIF well and switch to SUMIFS. Always double-check your argument order. The criteria ranges and sum_range must all cover the same number of rows. Using full column references like A:A for all ranges is the easiest way to ensure this. Criteria can use comparison operators, wildcards, and cell references combined with operators using the & operator: ">"&A1 means greater than the value in cell A1.
Examples
1
Sum the sales revenue for the North region with order values above 5000.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Revenue | SUMIFS Result |
| 2 | North | Widget A | Q1 | 7200 | =SUMIFS(D2:D11,A2:A11,"North",C2:C11,"Q1") |
| 3 | South | Widget B | Q1 | 8500 | |
| 4 | North | Widget C | Q2 | 3100 | |
| 5 | East | Widget A | Q1 | 6800 | |
| 6 | North | Widget B | Q1 | 9500 | 16700 |
| 7 | West | Widget C | Q2 | 5400 | |
| 8 | North | Widget A | Q2 | 4300 | |
| 9 | South | Widget B | Q1 | 7100 | |
| 10 | East | Widget C | Q1 | 6200 | |
| 11 | North | Widget B | Q2 | 8800 |
Row 2: 22200
— Three North rows have Revenue above 5000: 7200 + 9500 + 5500 = 22200.
Row 3 (North, 3100) is excluded because 3100 is below 5000 even though it is in the North region. Row 2 (South, 8500) is excluded because it is not in the North region even though the revenue exceeds 5000. Both conditions must be true simultaneously. Row 7 (North, 5500) is included because 5500 is greater than 5000 — use >= instead of > if you want to include exactly 5000.
2
Sum only the salaries of Sales employees who earn more than 50,000.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Department | Salary | SUMIFS Result |
| 2 | Alice | Sales | 62000 | =SUMIFS(C2:C11,B2:B11,"Sales",C2:C11,">50000") |
| 3 | Bob | Marketing | 48000 | |
| 4 | Carol | Sales | 55000 | 175000 |
| 5 | David | Sales | 45000 | |
| 6 | Eve | Engineering | 71000 | |
| 7 | Frank | Marketing | 53000 | |
| 8 | Grace | Sales | 58000 | |
| 9 | Hank | HR | 42000 | |
| 10 | Iris | Engineering | 67000 | |
| 11 | James | Sales | 39000 |
Row 2: 175000
— Alice (62k), Carol (55k), and Grace (58k) are Sales and over 50k. 62000+55000+58000 = 175000.
You can use the same column as both a criteria_range and the sum_range. SUMIFS applies criteria before summing.
3
Sum order values that fall within Q1 2024 using two date conditions.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Customer | Amount | Date | Q1 Total |
| 2 | ORD-001 | Acme | 4500 | 2024-01-15 | =SUMIFS(C2:C11,D2:D11,">="&DATE(2024,1,1),D2:D11,"<"&DATE(2024,4,1)) |
| 3 | ORD-002 | Beta | 2300 | 2024-02-08 | |
| 4 | ORD-003 | Gamma | 8100 | 2023-12-20 | 22400 |
| 5 | ORD-004 | Delta | 5600 | 2024-03-30 | |
| 6 | ORD-005 | Epsilon | 3200 | 2024-04-05 | |
| 7 | ORD-006 | Zeta | 1800 | 2024-01-28 | |
| 8 | ORD-007 | Eta | 6700 | 2024-02-14 | |
| 9 | ORD-008 | Theta | 9400 | 2023-11-30 | |
| 10 | ORD-009 | Iota | 1500 | 2024-03-12 | |
| 11 | ORD-010 | Kappa | 4100 | 2024-05-01 |
Row 2: 22400
— ORD-001 (4500) + ORD-002 (2300) + ORD-004 (5600) + ORD-006 (1800) + ORD-007 (6700) + ORD-009 (1500) = 22400.
Apply two conditions to the same date column to create a date range. Use >= for the start and < for the day after the end.
Common use cases
1. Total salaries for employees in a specific department hired after a certain date
=SUMIFS(Salary,Department,"Engineering",HireDate,">"&DATE(2021,1,1))
2. Sum invoice amounts for a specific vendor in a given month for accounts payable reporting
=SUMIFS(InvoiceAmt,Vendor,"Acme Corp",InvoiceDate,">="&DATE(2024,1,1),InvoiceDate,"<="&DATE(2024,1,31))
3. Total expenses for a cost centre coded as capital expenditure above a threshold amount
=SUMIFS(Amount,CostCentre,"IT",Type,"CapEx",Amount,">10000")
4. Sum order quantities for a product category where stock is below the reorder level
=SUMIFS(Quantity,Category,"Electronics",StockLevel,"<50")
5. Total hours logged by a specific team on high-priority projects in a date range
=SUMIFS(Hours,Team,"Dev",Priority,"High",Date,">="&StartDate,Date,"<="&EndDate)
Common errors
#VALUE!
One of the criteria ranges is a different size from the sum_range or other criteria ranges. All ranges must cover exactly the same number of rows for SUMIFS to compare them row by row correctly.
Fix: Make all ranges the same size. The easiest approach is to use full column references like A:A for all ranges, which automatically cover the same rows. If you use bounded ranges make sure all start and end rows match.
Returns 0 when you expect a sum
Either no rows match all the conditions simultaneously, or there are data issues like extra spaces in text values, numbers stored as text in the sum range, or a date format mismatch.
Fix: Test each condition separately with a single SUMIF to identify which condition is filtering out all rows. Check for spaces with TRIM. Verify the sum_range contains actual numbers not text. Format date columns consistently.
Wrong argument order
Using SUMIF argument order in SUMIFS. In SUMIF the sum_range is the third argument. In SUMIFS it is the first argument. This is the most common SUMIFS mistake.
Fix: Remember: SUMIFS(sum_range, criteria_range1, criteria1, ...). The sum_range always comes first in SUMIFS.
Tips and variations
Reference criteria from cells for dynamic reports
Build interactive summary tables by referencing criteria from cells rather than hardcoding them in the formula. Put region names, dates, or thresholds in input cells and reference them in SUMIFS. Changing a single input cell updates the entire summary without touching any formulas. This is how professional financial models and dashboards are built.
=SUMIFS(C:C,A:A,F1,C:C,">"&G1)
Sum between two dates using two criteria on the same column
To sum rows where a date falls in a range, apply two criteria to the same date column. This pattern works for any numeric range not just dates. It is one of the most commonly needed SUMIFS patterns in monthly and quarterly financial reporting.
=SUMIFS(Revenue,OrderDate,">="&DATE(2024,1,1),OrderDate,"<="&DATE(2024,12,31))
Combine SUMIFS with partial text matching using wildcards
SUMIFS supports wildcards in text criteria. Use an asterisk to match any sequence of characters — useful for summing all rows in a product family, all vendors with a common prefix, or all cost codes in a category. Question mark matches exactly one character for more precise partial matching.
=SUMIFS(Revenue,ProductCode,"PRD-A*",Region,"North")
Excel vs Google Sheets
Excel vs Google Sheets
SUMIFS works identically in Microsoft Excel and Google Sheets. Same syntax, same argument order (sum_range first), same wildcard support, same comparison operators, same date handling, and same 127 criteria pair limit. Formulas copy between the two applications without any modification. This consistency makes SUMIFS one of the most reliably portable analytical formulas available. Teams who work across both Excel and Google Sheets can use the same SUMIFS formulas in both environments without maintaining separate versions.
Related reading
Frequently asked questions
SUMIF handles a single condition and has the sum_range as the third argument: =SUMIF(range,criteria,sum_range). SUMIFS handles multiple conditions and has the sum_range as the first argument: =SUMIFS(sum_range,range1,criteria1,range2,criteria2). SUMIFS is available from Excel 2007 onwards. For single conditions either works, but note the different argument order. Many users prefer SUMIFS even for single conditions because it is consistent when you later need to add more criteria.
SUMIFS uses AND logic — all conditions must be true simultaneously. For OR logic write separate SUMIFS formulas and add them. For example to sum revenue from either North or South regions: =SUMIFS(Revenue,Region,"North")+SUMIFS(Revenue,Region,"South"). If a row could match both conditions and you do not want to count it twice, subtract the overlap with another SUMIFS that includes both conditions. Alternatively use SUMPRODUCT for more complex OR logic in a single formula.
The most common causes are: no rows matching all conditions simultaneously — test each condition separately with a single SUMIF to identify which one filters everything out; numbers stored as text in the sum_range — SUMIFS cannot add text values so verify cells are true numbers; extra spaces in criteria values — use TRIM; and date format issues where dates in the criteria do not match the format stored in the data column.
Yes, SUMIFS is completely identical in both applications. Same syntax, same argument order, same wildcard support, same comparison operators. The sum_range always comes first in both. Formulas copy between Excel and Google Sheets without any changes. This makes SUMIFS one of the most portable analytical functions for teams who work across both platforms.
SUMIFS cannot directly reference ranges across multiple sheets in a single formula. The standard approach is to write a separate SUMIFS for each sheet and add them together. For large numbers of sheets consider creating a summary table on each sheet and then summing those summaries. In Excel you can use 3D references for simple sums but SUMIFS does not support 3D references. SUMPRODUCT with INDIRECT can achieve cross-sheet conditional summing but is complex to maintain.