=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple") adds the sales in C2:C7 on the rows where column A is North and column B is Apple. The range to add comes first, then one range and condition pair for every condition.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Total | |
| 2 | North | Apple | 120 | North, Apple | 150 | |
| 3 | South | Pear | 45 | North, over 50 | 200 | |
| 4 | North | Pear | 80 | North | 230 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 |
Two rows are North and Apple, 120 and 30, so F2 shows 150. F3 uses the sales column as both the sum range and a criteria range: North rows over 50. F4 has a single condition and works like SUMIF. Change B4 to Apple and F2 grows by 80.
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_rangeis the cells to add, and it comes first. In SUMIF it comes last:=SUMIF(A2:A7,"North",C2:C7). Mixing the two orders is a common SUMIFS mistake. If you copy a SUMIF and add a condition, move the sum range to the front.- Each condition is a pair: a range and a criterion, up to 127 pairs. The criteria work like SUMIF's:
"North",">50","*apple*","<>", or a cell joined to an operator,">="&G2. - A row is added only when every condition is true.
- Every range must be the same size, or SUMIFS returns
#VALUE!.
SUMIFS works with a single condition too, and writing it that way means a second condition can be added later without reordering the arguments.
SUMIFS with a date range
To total one month, or any period, use the date column twice: once with the start date, once with the end date. Keep the dates in cells.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Date | Setting | Value | |
| 2 | North | Apple | 120 | 2026-01-05 | Start | 2026-02-01 | |
| 3 | South | Pear | 45 | 2026-01-12 | End | 2026-02-28 | |
| 4 | North | Pear | 80 | 2026-02-03 | Total | 135 | |
| 5 | East | Apple | 55 | 2026-02-18 | North only | 80 | |
| 6 | South | Apple | 200 | 2026-03-02 | |||
| 7 | North | Apple | 30 | 2026-03-20 |
February has two orders, 80 and 55, so G4 shows 135, and G5 keeps only the North one. Change G3 to 2026-03-31 to take in March. If the dates carry times, "<="&G3 stops at 0:00 on the last day: use "<"&G3+1 as the end condition so orders later that day are counted.
To total a whole month from one date in a cell, let EOMONTH find the last day: =SUMIFS(C2:C7,D2:D7,">="&G2,D2:D7,"<="&EOMONTH(G2,0)).
SUMIFS with OR
The conditions in SUMIFS are always joined with AND. For North or South, give the region condition a list in braces: SUMIFS returns one total per item and SUM adds them.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Total | |
| 2 | North | Apple | 120 | North or South | 475 | |
| 3 | South | Pear | 45 | North or South, Apple | 350 | |
| 4 | North | Pear | 80 | Two SUMIFS | 475 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 |
F2 and F4 both give 475, everything except the East row. F3 combines the OR on regions with an AND on the product. Put the list on one criterion only: two lists in one SUMIFS pair their items position by position instead of trying every combination.
SUMIFS with blank criteria: an optional filter
A report often has filter cells that the reader may leave empty to mean "all". An empty cell used as a criterion does not mean "anything": Excel reads it as 0, which matches nothing in a text column. Swap it for the wildcard "*", which matches any text. Pick a region and a product from the lists, or clear them.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Filter | Value | ||
| 2 | North | Apple | 120 | Region | North | ||
| 3 | South | Pear | 45 | Product | |||
| 4 | North | Pear | 80 | Total | 230 | ||
| 5 | East | Apple | 55 | ||||
| 6 | South | Apple | 200 | ||||
| 7 | North | Apple | 30 |
With North picked and no product, G4 adds every North row: 230. Pick Apple in G3 and it drops to 150; clear G2 and it adds Apple from every region. "*" matches text only, so this works for text columns; a column of numbers needs a range instead, ">="&min and "<="&max.
To add the rows where a column is empty or filled, use "" and "<>" as the criteria. With a Paid date in column D, =SUMIFS(C2:C7,A2:A7,"North",D2:D7,"") adds the North sales that are not paid yet.
Practice: SUMIFS
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Total | |
| 2 | North | Apple | 120 | South, Apple | ||
| 3 | South | Pear | 45 | |||
| 4 | North | Pear | 80 | |||
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 | |||
| 8 | South | Apple | 75 |
Your turn: Add up the sales where the region is South and the product is Apple. Write the formula in F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Date | Setting | Value | |
| 2 | North | Apple | 120 | 2026-01-05 | Start | 2026-01-10 | |
| 3 | South | Pear | 45 | 2026-01-12 | End | 2026-03-05 | |
| 4 | North | Pear | 80 | 2026-02-03 | Total | ||
| 5 | East | Apple | 55 | 2026-02-18 | |||
| 6 | South | Apple | 200 | 2026-03-02 | |||
| 7 | North | Apple | 30 | 2026-03-20 | |||
| 8 | North | Plum | 65 | 2026-03-01 |
Your turn: Add up the North sales dated from G2 to G3, both days included. Write the formula in G4.
SUMIFS vs SUMIF vs SUMPRODUCT
| You need | Use |
|---|---|
| One condition | SUMIF(range,criteria,sum_range) or SUMIFS with one pair |
| Several conditions, all true | SUMIFS(sum_range,range1,criteria1,range2,criteria2) |
| Either of several values in one column | SUM(SUMIFS(sum_range,range,{"a","b"})) |
| A condition on a calculation (month of a date, one column compared with another, price times quantity) | SUMPRODUCT |
| The same conditions, but a count | COUNTIFS |
If a SUMIFS returns 0, check each condition alone with COUNTIFS on the same ranges: the condition whose count is 0 is the one that never matches, usually because of a trailing space, a number stored as text, or a cell written inside the quotes (">=G2" instead of ">="&G2).
Frequently Asked Questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last: =SUMIF(A2:A7,"North",C2:C7). SUMIFS takes any number of conditions and puts the sum range first: =SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple"). With one condition both give the same total.
How do I use SUMIFS with a date range?
Use the date column twice, with a start and an end: =SUMIFS(C2:C7,D2:D7,">="&G2,D2:D7,"<="&G3) adds the sales dated from G2 to G3, both included.
Can SUMIFS use OR logic?
Not directly: its conditions are joined with AND. Give one criterion a list and add the results: =SUM(SUMIFS(C2:C7,A2:A7,{"North","South"})) totals North and South.
Why does SUMIFS return #VALUE!?
The sum range and the criteria ranges are not the same size, for example C2:C7 with A2:A8. Unlike SUMIF, SUMIFS does not resize them; every range must have the same number of rows and columns.
How do I make a SUMIFS criterion optional?
Replace an empty filter cell with the wildcard "*", which matches any text: =SUMIFS(C2:C7,A2:A7,IF(G2="","*",G2)). When G2 is empty every region is included.
