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

ABCDEFG
1DateAmountFromTo
22026-01-101002026-02-012026-02-28300
32026-02-03250
42026-02-2050
52026-03-05400

In G2: =SUMIFS(B2:B5, A2:A5, ">="&D2, A2:A5, "<="&E2) → 300

250 on 3 February and 50 on 20 February.

How it works

  1. SUMIFS adds the amounts in B2:B5 on the rows that meet every condition.
  2. The date column is tested twice: on or after the start date, and on or before the end date.
  3. ">="&D2 joins the operator to the date in the cell. The operator must be in quotes; the cell must not.

Variations

  1. With the dates written in the formula

    ABCDEFG
    1DateAmountFromTo
    22026-01-101002026-02-012026-02-28300
    32026-02-03250
    42026-02-2050
    52026-03-05400

    In G2: =SUMIFS(B2:B5, A2:A5, ">="&DATE(2026,2,1), A2:A5, "<"&DATE(2026,3,1)) → 300

    DATE 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".

  2. A whole month, from its first day

    ABCDEFG
    1DateAmountFromTo
    22026-01-101002026-02-012026-02-28300
    32026-02-03250
    42026-02-2050
    52026-03-05400

    In G2: =SUMIFS(B2:B5, A2:A5, ">="&D2, A2:A5, "<="&EOMONTH(D2, 0)) → 300

    EOMONTH gives the last day of the month D2 is in.

  3. Count the payments instead

    ABCDEFG
    1DateAmountFromTo
    22026-01-101002026-02-012026-02-282
    32026-02-03250
    42026-02-2050
    52026-03-05400

    In G2: =COUNTIFS(A2:A5, ">="&D2, A2:A5, "<="&E2) → 2

Watch out for

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

Practise it

Related