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.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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.