SUMIFS Function in Excel
Quick answer
SUMIFS adds up the numbers in a range that meet every condition you give it. Reach for it when a plain SUM is too broad, like totalling one menu category or only the sales dated inside a BAS quarter. You can stack up to 127 conditions, and a row only counts when all of them match.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Works in: Excel 365, Excel 2024, 2021, 2019, 2016 and Excel for the web, plus Google Sheets with the same syntax. In Google Sheets, the Advanced example below needs SUMPRODUCT in place of SUM.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required? | What it does |
|---|---|---|
sum_range | Yes | The cells to add up, such as the Total column. It comes first in SUMIFS. |
criteria_range1 | Yes | The first range to test, such as the Category column. Must be the same size as sum_range. |
criteria1 | Yes | The condition for criteria_range1: text ("Coffee"), a number, a comparison in quotes (">=500") or a cell reference. |
criteria_range2, criteria2 | No | Extra range and condition pairs, up to 127 pairs in total. Default: none, so only the first condition applies. |
How SUMIFS works
Think of SUMIFS as a filter followed by a sum. It walks down the rows, checks each criteria range against its condition, and adds the sum_range value only for rows that pass every test. Rows that fail any test are skipped.
Mind the argument order. In SUMIFS the range you’re adding up comes first, then the range and condition pairs. In SUMIF it comes last. If you turn an old SUMIF into a SUMIFS by tacking a condition on the end, you’ll get a wrong total or an error, so move sum_range to the front.
A condition can be exact text ("Coffee"), a number, a comparison in quotes (">=500") or a wildcard, where * stands for any characters and ? for one. Every condition has to be true, so SUMIFS works on AND logic. For OR (this category or that one), see the Advanced example below.
Examples
All three examples use the Sales sheet from the Bluegum Café workbook. I’ve extended it to 12 rows of takings running from July into October, added Tea as a category, and added a GST column in H. The totals include GST, so H2 holds =ROUND(G2/11, 2), filled down. The data sits in A1:H13.
| Date | Day | Category | Item | Qty | Unit Price | Total (inc GST) | GST |
|---|---|---|---|---|---|---|---|
| 01/07/2025 | Tue | Coffee | Flat white | 42 | $5.20 | $218.40 | $19.85 |
| 01/07/2025 | Tue | Food | Banana bread | 18 | $6.50 | $117.00 | $10.64 |
| 01/07/2025 | Tue | Tea | English breakfast | 15 | $4.50 | $67.50 | $6.14 |
| 19/07/2025 | Sat | Coffee | Latte | 55 | $5.50 | $302.50 | $27.50 |
| 19/07/2025 | Sat | Food | Toasted sandwich | 24 | $11.00 | $264.00 | $24.00 |
| 14/08/2025 | Thu | Coffee | Flat white | 38 | $5.20 | $197.60 | $17.96 |
| 14/08/2025 | Thu | Tea | Chai latte | 20 | $5.50 | $110.00 | $10.00 |
| 06/09/2025 | Sat | Food | Banana bread | 22 | $6.50 | $143.00 | $13.00 |
| 06/09/2025 | Sat | Coffee | Long black | 30 | $4.80 | $144.00 | $13.09 |
| 30/09/2025 | Tue | Tea | English breakfast | 12 | $4.50 | $54.00 | $4.91 |
| 01/10/2025 | Wed | Coffee | Flat white | 40 | $5.20 | $208.00 | $18.91 |
| 01/10/2025 | Wed | Food | Toasted sandwich | 16 | $11.00 | $176.00 | $16.00 |
Basic: Total sales for one menu category
On the Sales sheet, type a category in J2 and put the formula in K2.
=SUMIFS(G2:G13, C2:C13, J2)With Coffee in J2, the result is $1,070.50, the five coffee rows added together. Change J2 to Food and it updates to $700.00. With one condition this does the same job as SUMIF, but writing it as SUMIFS means you can add a second condition later without rearranging anything.
Real-world: GST collected for a BAS quarter
On the Sales sheet, put the quarter start date in J5, the end date in K5, and the formula in L5.
=SUMIFS(H2:H13, A2:A13, ">="&J5, A2:A13, "<="&K5)With 01/07/2025 in J5 and 30/09/2025 in K5, the result is $147.09, the GST collected on sales for the September quarter. The Date column is tested twice: once for on or after the start, once for on or before the end. The sale on 30/09/2025 is counted and the two on 01/10/2025 are left for the next quarter. Change the two dates and you have any other quarter, with no need to touch the formula.
Advanced: Sum two categories at once
On the Sales sheet, put the formula in K8 to total all drinks, Coffee and Tea together.
=SUM(SUMIFS(G2:G13, C2:C13, {"Coffee","Tea"}))The result is $1,302.00. Here’s why it needs the extra SUM. SUMIFS conditions are AND, and a row’s category can’t be Coffee and Tea at the same time, so adding Tea as a second condition returns 0. Listing both in curly braces makes SUMIFS run once per item and hand back two totals, $1,070.50 for Coffee and $231.50 for Tea. SUM then adds those two together. It works in every Excel version, with no Ctrl+Shift+Enter needed.
Guru Tip
When a condition compares against a cell, the operator goes inside the quotes and the & and cell reference go outside them: ">="&J5. Type ">=J5" and Excel goes looking for the literal text J5, finds nothing and hands you 0 with no error to warn you. This is the one that trips most people up, me included, so it’s the first thing I check when a date range total comes back as zero.
Common errors
#VALUE!: ranges are different sizes
Cause: sum_range and a criteria range cover different numbers of rows, such as G2:G13 and C2:C12. Fix: Make every range start and end on the same rows, or turn the data into an Excel Table so the ranges grow together.
#VALUE!: the source workbook is closed
Cause: SUMIFS points at another workbook that isn’t open. It can’t read closed files. Fix: Open the other workbook, or rewrite the total with SUMPRODUCT, which does work on closed files.
0 instead of a total: nothing matched
Cause: This isn’t an error as far as Excel is concerned, which is why it’s easy to miss. Either no rows met your conditions or the cells being added hold text. The usual suspects are stray spaces ("Coffee " is not "Coffee"), numbers or dates stored as text, and an operator typed inside the quotes with the cell reference. Fix: Run COUNTIFS with the same conditions. If it also returns 0, the conditions aren’t matching, so clean the criteria column with TRIM and check the dates are real dates. If COUNTIFS finds rows but SUMIFS still gives 0, the numbers are stored as text: convert them with Data > Text to Columns or VALUE.
Grab the template: SUMIFS
[TEMPLATE BENEFIT: one sentence on what the template does for you]