How to / Sums and averages
How to sum values between two dates in Excel
Use =SUMIFS(B2:B5, A2:A5, ">="&D2, A2:A5, "<="&E2), with the start date in D2 and the end date in E2.
Payments in February
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Amount | From | To | |||
| 2 | 2026-01-10 | 100 | 2026-02-01 | 2026-02-28 | 300 | ||
| 3 | 2026-02-03 | 250 | |||||
| 4 | 2026-02-20 | 50 | |||||
| 5 | 2026-03-05 | 400 |
In G2: =SUMIFS(B2:B5, A2:A5, ">="&D2, A2:A5, "<="&E2) → 300
250 on 3 February and 50 on 20 February.
How it works
- SUMIFS adds the amounts in
B2:B5on the rows that meet every condition. - The date column is tested twice: on or after the start date, and on or before the end date.
">="&D2joins the operator to the date in the cell. The operator must be in quotes; the cell must not.
Variations
With the dates written in the formula
A B C D E F G 1 Date Amount From To 2 2026-01-10 100 2026-02-01 2026-02-28 300 3 2026-02-03 250 4 2026-02-20 50 5 2026-03-05 400 In G2:
=SUMIFS(B2:B5, A2:A5, ">="&DATE(2026,2,1), A2:A5, "<"&DATE(2026,3,1))→ 300DATE builds a real date from a year, a month and a day. "Before 1 March" is a safe way to say "to the end of February".
A whole month, from its first day
A B C D E F G 1 Date Amount From To 2 2026-01-10 100 2026-02-01 2026-02-28 300 3 2026-02-03 250 4 2026-02-20 50 5 2026-03-05 400 In G2:
=SUMIFS(B2:B5, A2:A5, ">="&D2, A2:A5, "<="&EOMONTH(D2, 0))→ 300EOMONTH gives the last day of the month D2 is in.
Count the payments instead
A B C D E F G 1 Date Amount From To 2 2026-01-10 100 2026-02-01 2026-02-28 2 3 2026-02-03 250 4 2026-02-20 50 5 2026-03-05 400 In G2:
=COUNTIFS(A2:A5, ">="&D2, A2:A5, "<="&E2)→ 2
Watch out for
- Dates stored as text. SUMIFS compares numbers. A date that sits on the left of its cell is text and is never "between" anything. Retype it, or convert the column with Data > Text to Columns.
- Times of day. A date with a time, such as 28 February at 3 pm, is later than 28 February. Use "before the day after" as the upper limit to include the whole last day.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- SUMIFS: Adds the cells that meet every one of several conditions.
- COUNTIFS: Counts the rows that meet every one of several conditions.
- DATE: Builds a date from a year, month and day.
- EOMONTH: The last day of the month, a number of months away.
- AVERAGEIFS: Averages the cells that meet every one of several conditions.