Combination
Year-end cut-off test with FILTER and SUM
Flag the sales invoices whose booking date and delivery date fall on different sides of year end and total them: two comparisons with the year-end date give TRUE or FALSE per date, FILTER keeps the invoices where the two differ, and SUM adds them up.
Functions used:FILTERSUMExcel 2021 or later (This combination needs Excel 2021 or later)
Worked example
Invoices booked and delivered on different sides of year end
Scenario
For the audit of Veldhuis Installatie BV (year end 31 December 2025) you do the revenue cut-off test. From the sales ledger you have seven invoices around year end, with booking date and delivery date.
You want to know which invoices were booked in one year and delivered in the other, and how much revenue that is in total, so you can ask the client whether the revenue is recognised in the right period. This needs Excel 2021 or Microsoft 365 (FILTER).
The data
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Booking date | Delivery date | Amount | Year end | 31 Dec 2025 | ||
| 2 | 25-0988 | Hofman Techniek BV | 18 Dec 2025 | 16 Dec 2025 | €8,400.00€ 8.400,00 | Cut-off exceptions | €45,550.00€ 45.550,00=SUM(FILTER(E2:E8, (C2:C8<=H1)<>(D2:D8<=H1), 0))=SUM(FILTER(E2:E8; (C2:C8<=H1)<>(D2:D8<=H1); 0)) | ||
| 3 | 25-0997 | De Bruin Metaal BV | 29 Dec 2025 | 6 Jan 2026 | €14,250.00€ 14.250,00 | ||||
| 4 | 25-1003 | Jansen Logistiek BV | 31 Dec 2025 | 31 Dec 2025 | €5,600.00€ 5.600,00 | ||||
| 5 | 26-0002 | Kuiper Bouw BV | 3 Jan 2026 | 30 Dec 2025 | €9,800.00€ 9.800,00 | ||||
| 6 | 25-1006 | Smit Verpakking BV | 30 Dec 2025 | 2 Jan 2026 | €21,500.00€ 21.500,00 | ||||
| 7 | 26-0004 | Mulder Groep BV | 5 Jan 2026 | 4 Jan 2026 | €3,150.00€ 3.150,00 | ||||
| 8 | 25-1001 | Van Dijk Transport BV | 22 Dec 2025 | 23 Dec 2025 | €6,700.00€ 6.700,00 | ||||
| 9 |
Swipe sideways to see all columns
Formula in H2
=SUM(FILTER(E2:E8,(C2:C8<=H1)<>(D2:D8<=H1), 0))
=SUM(FILTER(E2:E8;(C2:C8<=H1)<>(D2:D8<=H1); 0))
Result: €45,550.00€ 45.550,00
How the pieces fit together
Watch out: Dates stored as text (a pasted export) or an empty delivery date give wrong answers without a warning: an empty cell counts as 0 and so as before year end.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=C2:C8<=H1
=C2:C8<=H1
ReferenceBooking date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to closeCompares each booking date with the year end in
H1(31 December 2025). The result is one column of seven values: TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE. FALSE means the invoice was booked in 2026 (3 and 5 January).=D2:D8<=H1
=D2:D8<=H1
ReferenceDelivery date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to closeThe same test on the delivery dates: TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE. FALSE means the goods were delivered in 2026 (2, 4 and 6 January).
=(C2:C8<=H1)<>(D2:D8<=H1)
=(C2:C8<=H1)<>(D2:D8<=H1)
ReferenceBooking date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to close<>Not equal toPress Escape to closeReferenceDelivery date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to closeThe operator
<>asks per row whether the two answers are different. The 2nd, 4th and 5th invoice differ (TRUE against FALSE or the reverse), the others agree: FALSE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE.This is the include list for FILTER, and it also catches both directions of a cut-off error.
=FILTER(A2:E8,
(C2:C8<=H1)<>(D2:D8<=H1), "No exceptions") =FILTER(A2:E8;
(C2:C8<=H1)<>(D2:D8<=H1); "No exceptions") FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.7 rows by 5 columnsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Booking date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to close<>Not equal toPress Escape to closeReferenceDelivery date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeFILTER keeps the full rows where the include list is TRUE:
25-0997(14,250),26-0002(9,800) and25-1006(21,500). That is the list of exceptions you take to the client. If no invoice differed, the cell would show the message.=SUM(FILTER(E2:E8,
(C2:C8<=H1)<>(D2:D8<=H1), 0)) =SUM(FILTER(E2:E8;
(C2:C8<=H1)<>(D2:D8<=H1); 0)) FILTERReturns every row of a range that meets a condition.Used here as: number1The first number, cell or range to add.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.Amount column, 7 cellsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Booking date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to close<>Not equal toPress Escape to closeReferenceDelivery date column, 7 cellsPress Escape to close<=Less than or equal toPress Escape to closeReferenceInput cell: 31 Dec 2025Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeFILTER now returns only the amount column for the three exceptions, and SUM adds them: 14,250 + 9,800 + 21,500 = 45,550. The 0 as third argument of FILTER makes the total 0, instead of #CALC!, when there are no exceptions.
Step by step
C2:C8<=H1tests each booking date against 31 December 2025: 18 Dec yes, 29 Dec yes, 31 Dec yes, 3 Jan no, 30 Dec yes, 5 Jan no, 22 Dec yes. Result: TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE.D2:D8<=H1does the same for the delivery dates: 16 Dec yes, 6 Jan no, 31 Dec yes, 30 Dec yes, 2 Jan no, 4 Jan no, 23 Dec yes. Result: TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE.- The
<>between the two lists compares them row by row: TRUE/TRUE no, TRUE/FALSE yes, TRUE/TRUE no, FALSE/TRUE yes, TRUE/FALSE yes, FALSE/FALSE no, TRUE/TRUE no. Result: FALSE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE. - FILTER(E2:E8, ...) keeps the amounts of the 2nd, 4th and 5th invoice (invoices
25-0997,26-0002and25-1006): 14,25014.250, 9,8009.800 and 21,50021.500. - SUM adds the three amounts: 14,25014.250 + 9,8009.800 + 21,50021.500 = 45,55045.550. That is the revenue with a possible cut-off error, to discuss with the client.
Why it works
A comparison with a cell holding the year end turns a whole column of dates into a column of TRUE and FALSE: before or on year end, or after. Comparing the two columns with <> is TRUE exactly when booking and delivery are on different sides, in either direction, so no IF or AND is needed per row.
FILTER keeps the amounts of those rows and SUM adds them. Because the year end sits in H1, you reuse the test next year by changing one cell.
Watch out for
- A date equal to year end counts as the old year because of
<=. Invoice25-1003(booked and delivered on 31 December) is therefore not an exception. Use<only if your year end is defined as the first day of the next period. - The total mixes both directions. Booked in 2025 and delivered in 2026 (
25-0997and25-1006, 35,75035.750) overstates 2025 revenue; booked in 2026 and delivered in 2025 (26-0002, 9,8009.800) understates it. Look at the two groups apart before you conclude anything on the misstatement. - Before you trust the result, check with COUNT or COUNTBLANK that all seven dates are real dates.
Variations
=SUMIFS(E2:E8,C2:C8, "<="&H1, D2:D8, ">"&H1) + SUMIFS(E2:E8, C2:C8, ">"&H1, D2:D8, "<="&H1)
=SUMIFS(E2:E8;C2:C8; "<="&H1; D2:D8; ">"&H1) + SUMIFS(E2:E8; C2:C8; ">"&H1; D2:D8; "<="&H1)
The version for Excel 2007 and later, without FILTER: one SUMIFS for each direction. Booked on or before year end and delivered after: 14,25014.250 + 21,50021.500 = 35,75035.750. Booked after and delivered on or before: 9,8009.800. Total 45,55045.550, and you see the two directions separately.
=SUMPRODUCT(((C2:C8<=H1)<>(D2:D8<=H1))*E2:E8)
=SUMPRODUCT(((C2:C8<=H1)<>(D2:D8<=H1))*E2:E8)
The same test inside SUMPRODUCT: the TRUE and FALSE values are multiplied by the amounts and added. Result 45,55045.550, and it works without FILTER, but it only returns the total, not the list of invoices.
=SUM(FILTER(E2:E8,(C2:C8<=H1)*(D2:D8>H1), 0))
=SUM(FILTER(E2:E8;(C2:C8<=H1)*(D2:D8>H1); 0))
Only one direction: booked on or before year end and delivered after it, so possible overstated 2025 revenue. 14,25014.250 + 21,50021.500 = 35,75035.750.
Functions used