SUMIFS Function in Excel and Google Sheets
Purpose
Sum values that meet two or more conditions simultaneously using AND logic
Return value
A number — the sum of all values in sum_range where every criteria pair matches
SUMIFS is the formula I reach for when SUMIF is not enough — when I need to filter by department AND region, by product AND date range, by customer AND order status. Every additional criteria pair that I add to SUMIFS narrows the rows further, and all conditions must pass simultaneously for a row to be included in the total. That AND logic is exactly what most business reporting requires. Sales for the North region specifically in Q1. Expenses for a specific vendor only in a specific month. Orders above a minimum value only for a specific customer. All of these are SUMIFS. The one thing to commit to memory before anything else is that sum_range goes first in SUMIFS — this is the opposite of SUMIF where sum_range is the third and last argument. Getting that right from the start prevents the single most common SUMIFS mistake. Once that is locked in, SUMIFS becomes the standard conditional total formula for every dashboard and report.
Syntax
✓ Excel 2007+
✓ Google Sheets
= Same syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range comes FIRST in SUMIFS — unlike SUMIF where it is the third argument. This is the most common source of errors when switching between the two functions.
Arguments
| Argument | Required | Description |
|---|---|---|
| sum_range | Required | The column containing values to add. This is the FIRST argument in SUMIFS — note it is last in SUMIF. Must be the same size as all criteria_ranges. |
| criteria_range1 | Required | The first column to check against criteria1. Must be the same size as sum_range. |
| criteria1 | Required | The condition to match against criteria_range1. Same syntax as SUMIF criteria — text in quotes, numbers, comparisons, wildcards, or cell references. |
| criteria_range2 / criteria2 | Optional | Additional condition pairs — each adds another AND condition. All must match for a row to be summed. Up to 127 criteria pairs supported. |
How it works
SUMIFS evaluates each row against all criteria pairs simultaneously. A row is added to the running total only when every single criteria pair matches — criteria_range1 matches criteria1 AND criteria_range2 matches criteria2 AND any additional pairs also match. All ranges provided to SUMIFS — sum_range and every criteria_range — must cover exactly the same number of rows so that corresponding rows align correctly. Using full column references like C:C instead of C2:C100 satisfies this automatically and includes all new rows added to the data. For date range filtering, apply two criteria pairs to the same column — one for the lower boundary using >= and one for the upper boundary using <=. Always concatenate comparison operators with date values: ">="&DATE(2024,1,1) is correct, >=DATE(2024,1,1) is not. To build a dynamic summary table where the criteria come from cells rather than hardcoded values, reference cells in the criteria arguments and lock them appropriately with dollar signs when copying the formula across rows and columns.
Examples
1
Sum sales for the North region in the Sales department.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Department | Sales |
| 2 | North | Sales | 12500 |
| 3 | South | Sales | 9800 |
| 4 | North | Marketing | 0 |
| 5 | North | Sales | 15200 |
| 6 | West | Sales | 7100 |
| 7 | South | Marketing | 0 |
| 8 | North | Sales | 8300 |
| 9 | East | Sales | 11400 |
| 10 | North | Engineering | 0 |
Row 2: 36000
— Rows 1, 4, and 7 match both North AND Sales: 12500+15200+8300 = 36000.
Row 3 is North but Marketing — fails the second condition. Row 2 is Sales but South — fails the first. Both are excluded. Every condition must pass.
2
Sum all expenses between two dates entered in cells E1 and F1.
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Category | Date | Amount | Start | End | |
| 2 | Travel | 2024-01-05 | 850 | 2024-01-10 | 2024-01-31 | |
| 3 | Software | 2024-01-08 | 1299 | |||
| 4 | Travel | 2024-01-12 | 1200 | |||
| 5 | Office | 2024-01-15 | 75 | |||
| 6 | Travel | 2024-01-18 | 620 | |||
| 7 | Software | 2024-01-22 | 450 | |||
| 8 | Training | 2024-01-25 | 1980 | |||
| 9 | Office | 2024-01-28 | 45 | |||
| 10 | Travel | 2024-01-30 | 1100 |
Row 2: 5470
— Dates from 10 Jan to 31 Jan: 1200+75+620+450+1980+45+1100 = 5470.
The ">="&E1 pattern concatenates the comparison operator with the date in E1. Change the dates in E1 and F1 and the sum updates automatically.
3
Sum Q1 sales for a specific rep.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Rep | Quarter | Sales |
| 2 | 2024-01-15 | Alice | Q1 | 12500 |
| 3 | 2024-02-20 | Bob | Q1 | 9800 |
| 4 | 2024-03-10 | Alice | Q1 | 15200 |
| 5 | 2024-04-05 | Alice | Q2 | 7100 |
| 6 | 2024-05-12 | Bob | Q2 | 8300 |
| 7 | 2024-06-18 | Alice | Q2 | 11400 |
| 8 | 2024-07-22 | Alice | Q3 | 9900 |
| 9 | 2024-08-14 | Bob | Q3 | 6700 |
| 10 | 2024-09-30 | Alice | Q3 | 14200 |
Row 2: 27700
— Alice in Q1: 12500 + 15200 = 27700. Bob Q1 row and Alice other quarters excluded.
A third criteria pair could be added to also filter by year, region, or product. SUMIFS handles up to 127 condition pairs.
Common use cases
1. Sum total revenue by region and product category for a multi-dimensional sales dashboard
=SUMIFS(D:D,A:A,G2,B:B,H2)
2. Total all expenses charged to a specific vendor within a particular calendar month using date boundaries
=SUMIFS(C:C,A:A,"Vendor Name",B:B,">="&DATE(2024,3,1),B:B,"<"&DATE(2024,4,1))
3. Sum only the orders from a specific customer that exceed a minimum order value threshold
=SUMIFS(C:C,A:A,"Customer A",C:C,">1000")
4. Build a matrix summary table with department names down rows and quarters across columns as dynamic criteria
=SUMIFS($D:$D,$B:$B,$G2,$C:$C,H$1)
5. Sum all transactions within a date range where the start and end dates are stored in two reference cells
=SUMIFS(C:C,B:B,">="&E1,B:B,"<="&F1)
Common errors
Returns zero when the data clearly contains matching rows
Usually a data type mismatch in one of the criteria columns — numbers stored as text look correct on screen but cannot be matched by numeric criteria. Also check that all ranges cover exactly the same number of rows, that comparison operator syntax is correct (operators must be inside quotes and concatenated with values), and that no criteria_range accidentally covers a different set of rows than sum_range.
Fix: Check cell alignment in criteria columns — left-aligned numbers are stored as text. Use full column references to ensure size consistency. Verify comparison criteria syntax: ">="&DATE(2024,1,1) not >=DATE(2024,1,1).
All ranges must be the same size
SUMIFS requires sum_range and every criteria_range to cover the same number of rows. If sum_range is C2:C100 but a criteria_range is A1:A50, the ranges are misaligned and results will be wrong or an error will occur.
Fix: Use full column references (C:C, A:A, B:B) for all ranges in every SUMIFS formula. This automatically ensures all ranges are the same size and covers all rows in the dataset regardless of how many rows exist.
#VALUE! error with date comparison criteria
Date comparison criteria must be formatted as text strings with the operator inside quotes concatenated with the date value. Writing >=DATE(2024,1,1) without quotes around the operator causes a #VALUE! error.
Fix: Always wrap the comparison operator in quotes and concatenate it with the date: ">="&DATE(2024,1,1) or ">="&A1 where A1 contains a date value.
Tips and variations
Memorise that sum_range is the FIRST argument in SUMIFS not the last
The most common SUMIFS mistake is writing the sum_range last as you would in SUMIF. In SUMIFS it must always be the very first argument before any criteria pairs. =SUMIFS(C:C,A:A,"North",B:B,"Sales") — C:C is sum_range, A:A and B:B are criteria ranges.
=SUMIFS(C:C,A:A,"North",B:B,"Sales")
Reference cells for criteria to build a dynamic cross-tabulation summary
Instead of hardcoding region names and quarter labels, put them in row and column headers and reference those cells. Lock one dimension and leave the other free so the formula can be copied across both rows and columns to fill an entire summary matrix automatically.
=SUMIFS($D:$D,$B:$B,$G2,$C:$C,H$1)
Apply two criteria pairs to the same column for date range filtering
To sum within a date range, use the same criteria_range twice with different criteria — one sets the lower boundary using >= and one sets the upper boundary using <=. Reference cells containing the dates to make the range easy to change without editing formulas.
=SUMIFS(C:C,B:B,">="&E1,B:B,"<="&F1)
Excel vs Google Sheets
Excel vs Google Sheets
SUMIFS works identically in Excel 2007+ and all versions of Google Sheets. Same argument order, same criteria syntax, same AND logic. Formulas copy between the two without changes. For Excel 2003, SUMIFS is not available — use SUMPRODUCT as an alternative.
Related reading
Frequently asked questions
SUMIF handles exactly one condition — one range, one criteria. SUMIFS handles two or more conditions simultaneously using AND logic, where every condition must match for a row to be included in the sum. There is also a syntax difference: in SUMIF the sum_range comes last as the third argument, while in SUMIFS the sum_range comes first as the first argument. This argument order difference is the most common source of errors when switching between the two functions. Use SUMIF when you have one condition, SUMIFS when you have two or more.
SUMIFS uses AND logic — every condition pair must match simultaneously for a row to be included. A row where department is Sales AND region is North qualifies. A row where department is Sales but region is West does not qualify if the North condition is applied. To achieve OR logic — sum rows where any one of several conditions is true — add separate SUMIFS formulas together: =SUMIFS(C:C,A:A,"East",B:B,"Q1")+SUMIFS(C:C,A:A,"West",B:B,"Q1") sums Q1 sales from both East and West.
Yes — use comparison operators concatenated with DATE functions or cell references containing dates. =SUMIFS(C:C,B:B,">="&DATE(2024,1,1),B:B,"<="&DATE(2024,3,31)) sums column C for dates in Q1 2024. To reference start and end dates stored in cells, use =SUMIFS(C:C,B:B,">="&D1,B:B,"<="&E1) where D1 and E1 contain the boundary dates. Change D1 and E1 and the sum updates automatically.
The most frequent cause is a data type mismatch in one of the criteria columns. Numbers stored as text in a column that should contain numbers are left-aligned in cells and cannot be matched by numeric criteria even though they look identical on screen. Also verify that all ranges — sum_range and every criteria_range — cover exactly the same number of rows. Different-sized ranges cause misalignment and produce wrong results without any error message.
Yes — the asterisk * matches any sequence of characters and the question mark ? matches exactly one character in text criteria. =SUMIFS(C:C,A:A,"North*",B:B,">1000") sums column C where column A starts with North — matching North, North East, North West, North Central — AND column B exceeds 1000. Both conditions must pass simultaneously for each row to be included.