Dates and reporting
SUMIFS between two dates: total amounts inside a reporting window
Add invoice payments on or after a start date and on or before an end date by joining comparison operators to boundary cells with &. Verify both endpoints and channel filters.
Five wholesale payments straddling April 2026
A small book publisher records settled invoices with whole calendar dates in column A, sales channel in column B, and amount in column C. To verify boundary handling, the list includes 31 March (one day early), 1 April (first day), 30 April (last day), and 1 May (one day late).
| Settled date (A) | Channel (B) | Amount (C) |
|---|---|---|
| 31 March 2026 | Trade | 310 |
| 1 April 2026 | Trade | 420 |
| 10 April 2026 | Direct | 190 |
| 30 April 2026 | Trade | 280 |
| 1 May 2026 | Trade | 500 |
Join >= and <= to the start and end cells
Store the period start (1 April 2026) in E2, the period end (30 April 2026) in F2, and the channel Trade in G2. List the sum range C2:C6 first, then enter A2:A6 twice—once with ">="&E2 and once with "<="&F2—followed by the channel test.
=SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<="&F2,B2:B6,G2)Verify the total by hand
The Trade total for April is 700: row 3 on 1 April (420) plus row 5 on 30 April (280). Row 2 (310) is excluded because 31 March is earlier than E2. Row 6 (500) is excluded because 1 May is later than F2. Row 4 (190) falls inside April, so removing the channel filter gives an all-channel April total of 420 + 190 + 280 = 890.
=SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<="&F2)Two silent zero traps: quoted cell names and reversed dates
Never place the cell reference inside the quotation marks. Writing ">=E2" asks SUMIFS to compare dates against the literal characters E2, which matches nothing and quietly returns 0 instead of an error.
Similarly, if someone puts 30 April in E2 and 1 April in F2 by mistake, no date can be simultaneously >= 30 April and <= 1 April, so SUMIFS again returns 0. When a report unexpectedly shows 0, check that E2 <= F2 and that column A holds numeric dates rather than unparsed text.
How the practice serial numbers map to calendar dates
In the 1900 spreadsheet date system used below, whole calendar days are consecutive integers: 31 March 2026 is 46112, 1 April 2026 is 46113, 10 April 2026 is 46122, 30 April 2026 is 46142, and 1 May 2026 is 46143. (If your column includes timestamps such as 18:00 on the final day, use < 46143 instead of <= 46142 so afternoon entries on 30 April are included.)
Change A6 from 46143 (1 May) to 46142 (30 April) in the practice grid: the April Trade total rises from 700 to 1200.
Try the example
Use the original values or change them to test your understanding.