SUMIFS Function
Excel Guides / Math & Statistics
Math & Statistics
Add up only the rows that match your conditions, from a single category to a date range with several filters.
Updated 12 Oct 2026 Ā· 6 min read Ā· By Sky Dashboards
SUMIFS adds up the numbers in one column, but only on the rows that meet every condition you give it. It answers the questions a small business asks every week: how much did prints make online, what came in during February, which category is carrying the month. It sits behind most summary tables, and once you know its few rules it rarely surprises you.
In this guide
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required? | What it does |
|---|---|---|
sum_range |
Required | The cells to add up. Text and empty cells in this range are ignored. |
criteria_range1 |
Required | The cells to test against the first condition. |
criteria1 |
Required | The condition, such as "Prints", ">=1000" or a cell reference. |
criteria_range2, criteria2, ... |
Optional | More pairs of range and condition. You can add up to 127 pairs. |
A row is added only when it passes every condition. That makes SUMIFS an AND filter: category is Prints and channel is Online.
-
Every range must be the same size and shape as
sum_range. Ifsum_rangeis D2:D9, each criteria range needs eight rows too, or SUMIFS returns#VALUE!. -
Text conditions are not case-sensitive.
"prints"matches Prints. -
Conditions with an operator are written as text, so
">=1000"goes in quotes. A plain number or a cell reference does not need them.
Sum with one and two conditions
Here is a small stationery shop's sales log. Each row is one sale, with the category, where it sold and the amount.
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | Date | Category | Channel | Amount | |
| 2 | 05-Jan-26 | Prints | Online | $240 | |
| 3 | 09-Jan-26 | Cards | Market | $85 | |
| 4 | 14-Jan-26 | Prints | Market | $180 | |
| 5 | 22-Jan-26 | Notebooks | Online | $310 | |
| 6 | 03-Feb-26 | Cards | Online | $120 | |
| 7 | 11-Feb-26 | Prints | Online | $425 | |
| 8 | 17-Feb-26 | Notebooks | Market | $150 | |
| 9 | 25-Feb-26 | Cards | Market | $95 | |
| 10 | Prints online | $665 |
D10 adds the Amount column only where Category is Prints and Channel is Online: $240 + $425.
Read it left to right: sum D2:D9, where B2:B9 is Prints, and where C2:C9 is Online. Drop the second pair and you get every prints sale.
=SUMIFS(D2:D9,B2:B9,"Prints")All prints sales: $240 + $180 + $425 = $845.
Tip
Keep the source data in an Excel Table. A formula like =SUMIFS(Sales[Amount],Sales[Category],"Prints") reads clearly and picks up new rows automatically.
Criteria with operators and cell references
Put the comparison operator inside the quotes, in front of the value. SUMIFS understands =, <>, >, <, >= and <=.
| Criteria | What it matches |
|---|---|
"Prints" |
Cells equal to Prints, in any case |
">=1000" |
Numbers of 1,000 or more |
"<>Closed" |
Anything except Closed, including empty cells |
"<"&H1 |
Values below the number in H1 |
"" |
Empty cells |
"<>" |
Cells that are not empty |
On the sales log, =SUMIFS(D2:D9,D2:D9,">=200") returns $975, the three sales of $200 or more. The sum range and a criteria range can be the same column.
Take the condition from a cell
Typing the threshold into the formula means editing it every time the threshold changes. Put the value in a cell instead and join it to the operator with &:
=SUMIFS(D2:D9,D2:D9,">="&G1)With 200 in G1, this returns $975. Change G1 and the total follows.
Keep the reference outside the quotes
Writing ">=G1" compares every amount with the text G1, so the result is 0. The operator goes in quotes, the reference stays outside, and & joins them.
Partial matches with wildcards
Text criteria accept three special characters:
-
*stands for any number of characters."*book*"matches anything containing book. -
?stands for exactly one character."SKU-1??"matches codes such as SKU-104 and SKU-187. -
~cancels a wildcard, so"~*"matches a real asterisk.
=SUMIFS(D2:D9,B2:B9,"*book*")Every sale whose category contains book: $460 from the two Notebooks rows.
Note
Wildcards only work on text. They do not match numbers stored as numbers, so "10*" will not find 1050.
Sum between two dates
A date range is two conditions on the same column: on or after the start, and on or before the end. List the date column twice, once for each condition.
=SUMIFS(D2:D9,A2:A9,">="&G1,A2:A9,"<="&G2)With 01-Feb-26 in G1 and 15-Feb-26 in G2, the result is $545, the sales on 3 and 11 February.
=SUMIFS(D2:D9,A2:A9,">="&DATE(2026,1,1),A2:A9,"<="&DATE(2026,1,31))January written into the formula: $815.
Use DATE(year, month, day) rather than a typed date such as ">=3/1/2026". Typed dates are read using the computer's regional settings, so that criterion means 1 March in the US but 3 January in the UK, and the same file can give a different answer on a colleague's laptop.
Dates that include a time
If a date cell holds a time as well, such as 31-Jan-26 14:30, "<="&DATE(2026,1,31) leaves it out because it is later than midnight. Use "<"&DATE(2026,2,1) as the end condition instead.
For whole months, EOMONTH works out the end date from the start date, as in the summary below.
OR logic: one value or another
SUMIFS joins its conditions with AND. To total Prints or Cards, add two SUMIFS together:
=SUMIFS(D2:D9,B2:B9,"Prints")+SUMIFS(D2:D9,B2:B9,"Cards")$845 + $300 = $1,145.
With more options, an array constant is shorter. SUMIFS returns one total for each item in the braces, and SUM adds them up:
=SUM(SUMIFS(D2:D9,B2:B9,{"Prints","Cards"}))Also $1,145. Type the braces yourself. It is a normal formula, so a plain Enter is enough.
Watch out
Adding SUMIFS together only works when the options sit in the same column, because a row cannot be both Prints and Cards. For OR across two columns, such as Prints or sold online, the two totals come to $1,940 instead of the correct $1,275: the $665 of online prints is counted twice. Use SUMPRODUCT for that case.
SUMIF vs SUMIFS: the argument order trap
SUMIF is the older, single-condition version. It still works, but its arguments run in a different order:
| Function | Syntax | Sum range |
|---|---|---|
| SUMIF | =SUMIF(range, criteria, [sum_range]) |
Last, and optional |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, ...) |
First, and required |
The trap comes when you add a second condition to an existing SUMIF. Renaming it to SUMIFS is not enough: the sum range has to move to the front. If you only change the name, the arguments land in the wrong roles and the formula is rejected or adds the wrong column.
=SUMIF(B2:B9,"Prints",D2:D9)SUMIF: condition first, sum range last.
=SUMIFS(D2:D9,B2:B9,"Prints",C2:C9,"Online")SUMIFS: sum range first, then the pairs.
Tip
Use SUMIFS even when there is only one condition. The order never changes when you add another, and SUMIFS returns #VALUE! when the ranges are different sizes. SUMIF quietly resizes a mismatched sum range from its top-left cell, which can add the wrong cells without any warning.
Example: monthly sales by category
A grid with categories down the side and months across the top is a classic SUMIFS job. Put the first day of each month in the header row, write one formula, then fill it across and down.
| F | G | H | ||
|---|---|---|---|---|
| 1 | Category | Jan-26 | Feb-26 | |
| 2 | Prints | $420 | $425 | |
| 3 | Cards | $85 | $215 | |
| 4 | Notebooks | $310 | $150 | |
| 5 | Total | $815 | $790 |
G1 and H1 hold 1 January and 1 February 2026, formatted as mmm-yy. The formula in G2 fills G2:H4, and row 5 is a SUM.
The dollar signs do the work here. $F2 locks the column, so every cell reads its category from column F. G$1 locks the row, so every cell reads its month from the header. The data ranges are fully locked so they never move. If the dollar signs are new to you, see absolute and relative references.
EOMONTH(G$1,0) returns the last day of the header's month, so each column covers exactly one month. To add March, put 1 March in I1 and fill the formula one column further.
Tip
A small SUMIFS grid like this feeds the charts and KPI tiles in most Sky Dashboards templates, so the visuals never read the raw data directly. A PivotTable builds a similar grid with drag and drop, but SUMIFS gives you full control over the layout.
Frequently asked questions
Is SUMIFS case-sensitive?
No. Prints, prints and PRINTS all match the same rows. For a case-sensitive total, use SUMPRODUCT with the EXACT function.
Why does my SUMIFS return 0?
The usual causes are numbers or dates stored as text, extra spaces in the data or the criteria, a cell reference placed inside the quotes (">=G1" instead of ">="&G1), or a criteria range pointing at the wrong column.
How many conditions can SUMIFS handle?
Up to 127 pairs of criteria range and criteria. Most real formulas use two to four.
Does SUMIFS work with a closed workbook?
No. If the ranges point to another workbook that is closed, SUMIFS returns #VALUE!. Open the source file, or use SUMPRODUCT, which works with closed workbooks.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā