SUMIFS between two dates
PremiumIn F3, total the amounts of the orders dated from F1 to F2, both days included. Give SUMIFS the date column twice: ">="&F1 and "<="&F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order date | Customer | Amount | From | Mar 1, 2024 | ||
| 2 | Feb 27, 2024 | Northwind | 640 | To | Mar 31, 2024 | ||
| 3 | Mar 1, 2024 | Oakline | 310 | Total | |||
| 4 | Mar 8, 2024 | Northwind | 820 | ||||
| 5 | Mar 15, 2024 | Pinecrest | 150 | ||||
| 6 | Mar 22, 2024 | Oakline | 470 | ||||
| 7 | Mar 31, 2024 | Brightside | 290 | ||||
| 8 | Apr 2, 2024 | Northwind | 560 | ||||
| 9 | Apr 9, 2024 | Pinecrest | 380 | ||||
| 10 |
Premium lesson
Unlock SUMIFS and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
The two conditions on the date column keep the orders from Mar 1 to Mar 31, 2024: 310 + 820 + 150 + 470 + 290 = 2,040. The orders in February and April fall outside. This is the formula behind every monthly report: change F1 and F2 to total any other period.
Common mistake: Putting the sum range last, as in SUMIF. In SUMIFS the range to add up comes first.
The answer
- F3
=SUMIFS(1C2:C9,2A2:A9,3">="&F1,4A2:A9,5"<="&F2)
In plain words: Add up 1the amounts in C2:C9 on the rows where 2the order date in A2:A9 is 3on or after the date in F1 and 4the same date is 5on or before the date in F2.