Combinatie
Afgrenzingstoets rond het jaareinde met FILTER en SOM
Markeer de verkoopfacturen waarvan de boekingsdatum en de leverdatum aan verschillende kanten van het jaareinde liggen en tel ze op: twee vergelijkingen met de jaareinddatum geven per datum WAAR of ONWAAR, FILTER houdt de facturen over waarbij die twee verschillen en SOM telt ze op.
Gebruikte functies:FILTERSOMExcel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
Uitgewerkt voorbeeld
Facturen die aan verschillende kanten van het jaareinde zijn geboekt en geleverd
Situatie
Voor de controle van Veldhuis Installatie BV (boekjaar eindigt op 31 december 2025) voer je de afgrenzingstoets op de omzet uit. Uit het verkoopgrootboek heb je zeven facturen rond het jaareinde, met boekingsdatum en leverdatum.
Je wilt weten welke facturen in het ene jaar zijn geboekt en in het andere jaar zijn geleverd, en hoeveel omzet dat samen is, zodat je de klant kunt vragen of de omzet in de juiste periode is verantwoord. Hiervoor heb je Excel 2021, Microsoft 365 of nieuwer nodig (FILTER).
De gegevens
| Rij | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Factuur | Klant | Boekingsdatum | Leverdatum | Bedrag | Jaareinde | 31-12-2025 | ||
| 2 | 25-0988 | Hofman Techniek BV | 18-12-2025 | 16-12-2025 | €8,400.00€ 8.400,00 | Afgrenzingsafwijkingen | €45,550.00€ 45.550,00=SOM(FILTER(E2:E8, (C2:C8<=H1)<>(D2:D8<=H1), 0))=SOM(FILTER(E2:E8; (C2:C8<=H1)<>(D2:D8<=H1); 0)) | ||
| 3 | 25-0997 | De Bruin Metaal BV | 29-12-2025 | 06-01-2026 | €14,250.00€ 14.250,00 | ||||
| 4 | 25-1003 | Jansen Logistiek BV | 31-12-2025 | 31-12-2025 | €5,600.00€ 5.600,00 | ||||
| 5 | 26-0002 | Kuiper Bouw BV | 03-01-2026 | 30-12-2025 | €9,800.00€ 9.800,00 | ||||
| 6 | 25-1006 | Smit Verpakking BV | 30-12-2025 | 02-01-2026 | €21,500.00€ 21.500,00 | ||||
| 7 | 26-0004 | Mulder Groep BV | 05-01-2026 | 04-01-2026 | €3,150.00€ 3.150,00 | ||||
| 8 | 25-1001 | Van Dijk Transport BV | 22-12-2025 | 23-12-2025 | €6,700.00€ 6.700,00 | ||||
| 9 |
Veeg opzij om alle kolommen te zien
Formule in H2
=SOM(FILTER(E2:E8,(C2:C8<=H1)<>(D2:D8<=H1), 0))
=SOM(FILTER(E2:E8;(C2:C8<=H1)<>(D2:D8<=H1); 0))
Resultaat: €45,550.00€ 45.550,00
Hoe de onderdelen samenwerken
Let op: Datums die als tekst zijn opgeslagen (een geplakte export) of een lege leverdatum geven zonder waarschuwing een verkeerd antwoord: een lege cel telt als 0 en dus als vóór het jaareinde.
De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.
=C2:C8<=H1
=C2:C8<=H1
VerwijzingKolom Boekingsdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluitenVergelijkt elke boekingsdatum met het jaareinde in
H1(31 december 2025). Het resultaat is één kolom van zeven waarden: WAAR, WAAR, WAAR, ONWAAR, WAAR, ONWAAR, WAAR. ONWAAR betekent dat de factuur in 2026 is geboekt (3 en 5 januari).=D2:D8<=H1
=D2:D8<=H1
VerwijzingKolom Leverdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluitenDezelfde test op de leverdata: WAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR, WAAR. ONWAAR betekent dat de goederen in 2026 zijn geleverd (2, 4 en 6 januari).
=(C2:C8<=H1)<>(D2:D8<=H1)
=(C2:C8<=H1)<>(D2:D8<=H1)
VerwijzingKolom Boekingsdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluiten<>Niet gelijk aanDruk op Escape om te sluitenVerwijzingKolom Leverdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluitenDe operator
<>vraagt per rij of de twee antwoorden verschillen. De 2e, 4e en 5e factuur verschillen (WAAR tegen ONWAAR of andersom), de rest is gelijk: ONWAAR, WAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR.Dit is de lijst met voorwaarden voor FILTER, en hij vangt beide richtingen van een afgrenzingsfout.
=FILTER(A2:E8,
(C2:C8<=H1)<>(D2:D8<=H1), "Geen afwijkingen") =FILTER(A2:E8;
(C2:C8<=H1)<>(D2:D8<=H1); "Geen afwijkingen") FILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Lees meer over FILTERDruk op Escape om te sluitenmatrixDe rijen die je terug wilt: één kolom of een hele tabel.7 rijen bij 5 kolommenDruk op Escape om te sluitenopnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Kolom Boekingsdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluiten<>Niet gelijk aanDruk op Escape om te sluitenVerwijzingKolom Leverdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluitenals_leeg (optioneel)Wat je ziet als geen rij voldoet; zonder dit krijg je #BEREKENEN!.Druk op Escape om te sluitenFILTER houdt de volledige rijen over waar de lijst met voorwaarden WAAR is:
25-0997(14.250),26-0002(9.800) en25-1006(21.500). Dat is de lijst met afwijkingen die je bij de klant neerlegt. Verschilde geen enkele factuur, dan toont de cel de melding.=SOM(FILTER(E2:E8,
(C2:C8<=H1)<>(D2:D8<=H1), 0)) =SOM(FILTER(E2:E8;
(C2:C8<=H1)<>(D2:D8<=H1); 0)) FILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Hier gebruikt als: getal1Het eerste getal, de eerste cel of het eerste bereik om op te tellen.Lees meer over FILTERDruk op Escape om te sluitenmatrixDe rijen die je terug wilt: één kolom of een hele tabel.Kolom Bedrag, 7 cellenDruk op Escape om te sluitenopnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Kolom Boekingsdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluiten<>Niet gelijk aanDruk op Escape om te sluitenVerwijzingKolom Leverdatum, 7 cellenDruk op Escape om te sluiten<=Kleiner dan of gelijk aanDruk op Escape om te sluitenVerwijzingInvoercel: 31-12-2025Druk op Escape om te sluitenals_leeg (optioneel)Wat je ziet als geen rij voldoet; zonder dit krijg je #BEREKENEN!.Druk op Escape om te sluitenFILTER geeft nu alleen de bedragenkolom van de drie afwijkingen terug en SOM telt ze op: 14.250 + 9.800 + 21.500 = 45.550. De 0 als derde argument van FILTER zorgt dat het totaal 0 wordt in plaats van #BEREKENEN! als er geen afwijkingen zijn.
Stap voor stap
C2:C8<=H1toetst elke boekingsdatum aan 31 december 2025: 18 dec ja, 29 dec ja, 31 dec ja, 3 jan nee, 30 dec ja, 5 jan nee, 22 dec ja. Resultaat: WAAR, WAAR, WAAR, ONWAAR, WAAR, ONWAAR, WAAR.D2:D8<=H1doet hetzelfde voor de leverdata: 16 dec ja, 6 jan nee, 31 dec ja, 30 dec ja, 2 jan nee, 4 jan nee, 23 dec ja. Resultaat: WAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR, WAAR.- De
<>tussen de twee lijsten vergelijkt ze rij voor rij: WAAR/WAAR nee, WAAR/ONWAAR ja, WAAR/WAAR nee, ONWAAR/WAAR ja, WAAR/ONWAAR ja, ONWAAR/ONWAAR nee, WAAR/WAAR nee. Resultaat: ONWAAR, WAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR. - FILTER(E2:E8; ...) houdt de bedragen van de 2e, 4e en 5e factuur over (facturen
25-0997,26-0002en25-1006): 14,25014.250, 9,8009.800 en 21,50021.500. - SOM telt de drie bedragen op: 14,25014.250 + 9,8009.800 + 21,50021.500 = 45,55045.550. Dat is de omzet met een mogelijke afgrenzingsfout, om met de klant te bespreken.
Waarom het werkt
Een vergelijking met een cel waarin het jaareinde staat, maakt van een hele kolom datums een kolom WAAR en ONWAAR: op of voor het jaareinde, of erna. De twee kolommen met <> vergelijken is precies WAAR als boeking en levering aan verschillende kanten liggen, in beide richtingen, dus je hebt per rij geen ALS of EN nodig.
FILTER houdt de bedragen van die rijen over en SOM telt ze op. Omdat het jaareinde in H1 staat, gebruik je de toets volgend jaar opnieuw door één cel aan te passen.
Let op
- Een datum gelijk aan het jaareinde telt door
<=nog mee voor het oude jaar. Factuur25-1003(geboekt en geleverd op 31 december) is dus geen afwijking. Gebruik<alleen als je jaareinde is vastgelegd als de eerste dag van de volgende periode. - Het totaal mengt beide richtingen. Geboekt in 2025 en geleverd in 2026 (
25-0997en25-1006, 35,75035.750) maakt de omzet van 2025 te hoog; geboekt in 2026 en geleverd in 2025 (26-0002, 9,8009.800) maakt hem te laag. Bekijk de twee groepen apart voordat je een conclusie over de afwijking trekt. - Controleer met AANTAL of AANTAL.LEGE.CELLEN dat alle zeven datums echte datums zijn voordat je het resultaat vertrouwt.
Varianten
=SOMMEN.ALS(E2:E8,C2:C8, "<="&H1, D2:D8, ">"&H1) + SOMMEN.ALS(E2:E8, C2:C8, ">"&H1, D2:D8, "<="&H1)
=SOMMEN.ALS(E2:E8;C2:C8; "<="&H1; D2:D8; ">"&H1) + SOMMEN.ALS(E2:E8; C2:C8; ">"&H1; D2:D8; "<="&H1)
De versie voor Excel 2007 en later, zonder FILTER: een SOMMEN.ALS per richting. Op of voor het jaareinde geboekt en erna geleverd: 14,25014.250 + 21,50021.500 = 35,75035.750. Erna geboekt en op of voor het jaareinde geleverd: 9,8009.800. Samen 45,55045.550, en je ziet de twee richtingen apart.
=SOMPRODUCT(((C2:C8<=H1)<>(D2:D8<=H1))*E2:E8)
=SOMPRODUCT(((C2:C8<=H1)<>(D2:D8<=H1))*E2:E8)
Dezelfde test binnen SOMPRODUCT: de WAAR- en ONWAAR-waarden worden met de bedragen vermenigvuldigd en opgeteld. Resultaat 45,55045.550. Het werkt zonder FILTER, maar geeft alleen het totaal en niet de lijst met facturen.
=SOM(FILTER(E2:E8,(C2:C8<=H1)*(D2:D8>H1), 0))
=SOM(FILTER(E2:E8;(C2:C8<=H1)*(D2:D8>H1); 0))
Maar één richting: op of voor het jaareinde geboekt en erna geleverd, dus mogelijk te hoge omzet in 2025. 14,25014.250 + 21,50021.500 = 35,75035.750.
Gebruikte functies