Count the orders between two dates
PremiumIn F3, count the orders dated from F1 to F2, both days included. Use the date column twice in COUNTIFS and join each operator to its cell: ">="&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 | Orders | |||
| 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 COUNTIFS 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.
Why this works
Dates are numbers in Google Sheets, so "on or after Mar 1" is just ">=" joined to the date in F1, and "on or before Mar 31" is "<=" joined to F2. Five orders fall in March. Change F1 and F2 to count any other period.
Common mistake: Writing the cell inside the quotes, ">=F1". COUNTIFS then compares the dates with the text F1 and counts nothing.
The answer
- F3
=COUNTIFS(1A2:A9,2">="&F1,3A2:A9,4"<="&F2)
In plain words: Count the rows where 1the order date in A2:A9 is 2on or after the date in F1 and 3the same date is 4on or before the date in F2.