Combinatie
Ouderdomsanalyse debiteuren die aansluit op de controlerekening
Je telt het openstaande bedrag per ouderdomsklasse op met één SOMMEN.ALS over een klassentabel, telt de klassen op met SOM en controleert of het totaal aansluit op de controlerekening.
Gebruikte functies:SOMMEN.ALSSOM
Uitgewerkt voorbeeld
Ouderdomsanalyse van de debiteuren per 30 september 2026
Situatie
Voor Veldhuis Installatie BV heb je acht openstaande facturen uit het debiteurensubgrootboek met hun vervaldatums. Je wilt de ouderdomsanalyse voor de interimcontrole: het openstaande bedrag per klasse (nog niet vervallen, 1 tot en met 30, 31 tot en met 60, 61 tot en met 90 en meer dan 90 dagen) op de peildatum 30 september 2026 in F8, en een totaal dat je kunt aansluiten op het saldo van de controlerekening debiteuren in F9.
De klassen staan in een kleine tabel, zodat je ze kunt aanpassen zonder de formule te wijzigen.
De gegevens
| Rij | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Factuur | Vervaldatum | Openstaand bedrag | Klasse | Vanaf (dagen) | Tot en met (dagen) | ||
| 2 | 26-0501 | 12-10-2026 | €6,400.00€ 6.400,00 | Nog niet vervallen | -999-999 | 00 | ||
| 3 | 26-0502 | 02-10-2026 | €2,950.00€ 2.950,00 | 1 tot en met 30 dagen | 11 | 3030 | ||
| 4 | 26-0503 | 20-09-2026 | €4,200.00€ 4.200,00 | 31 tot en met 60 dagen | 3131 | 6060 | ||
| 5 | 26-0504 | 02-09-2026 | €7,800.00€ 7.800,00 | 61 tot en met 90 dagen | 6161 | 9090 | ||
| 6 | 26-0505 | 15-08-2026 | €8,500.00€ 8.500,00 | Meer dan 90 dagen | 9191 | 99999999 | ||
| 7 | 26-0506 | 20-07-2026 | €3,250.00€ 3.250,00 | |||||
| 8 | 26-0507 | 10-06-2026 | €9,600.00€ 9.600,00 | Peildatum | 30-09-2026 | |||
| 9 | 26-0508 | 25-05-2026 | €2,700.00€ 2.700,00 | Saldo controlerekening | 4540045400 | |||
| 10 | Totaal van de klassen | €45,400.00€ 45.400,00=SOM(SOMMEN.ALS($C$2:$C$9, $B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))=SOM(SOMMEN.ALS($C$2:$C$9; $B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6)) | ||||||
| 11 |
Veeg opzij om alle kolommen te zien
Formule in F10
=SOM(SOMMEN.ALS($C$2:$C$9,$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))
=SOM(SOMMEN.ALS($C$2:$C$9;$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6))
Resultaat: €45,400.00€ 45.400,00
Hoe de onderdelen samenwerken
Let op: Een factuur zonder vervaldatum of met een vervaldatum die als tekst is opgeslagen voldoet aan geen enkel datumcriterium, dus valt uit alle klassen en komt het totaal onder het saldo van de controlerekening uit.
De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.
=SOMMEN.ALS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G4, $B$2:$B$9, "<="&$F$8-$F4) =SOMMEN.ALS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G4; $B$2:$B$9; "<="&$F$8-$F4) SOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Openstaand bedrag, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".">=": vergelijkingenDruk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingInvoercel: 60Druk op Escape om te sluitencriteriumbereik2 (optioneel)Bereik voor nog een voorwaarde; tot 127 van zulke paren.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium2 (optioneel)De test voor criteriumbereik2, op dezelfde manier geschreven als criterium1.Druk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingInvoercel: 31Druk op Escape om te sluitenEén klasse, die in rij 4 (31 tot en met 60 dagen over tijd). De peildatum 30 september 2026 min 60 dagen is 1 augustus 2026 en min 31 dagen is 30 augustus 2026, dus SOMMEN.ALS telt de openstaande bedragen op van facturen met een vervaldatum van 1 tot en met 30 augustus. Alleen
26-0505(vervalt 15 augustus) valt in dat venster: 8.500.Vul de formule naar beneden aan en elke klasse krijgt een eigen totaal, want de dollartekens leggen de factuurlijst en de peildatum vast terwijl het rijnummer met de klasse meeloopt.
=SOMMEN.ALS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6) =SOMMEN.ALS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6) SOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Openstaand bedrag, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".">=": vergelijkingenDruk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingKolom G, 5 cellenDruk op Escape om te sluitencriteriumbereik2 (optioneel)Bereik voor nog een voorwaarde; tot 127 van zulke paren.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium2 (optioneel)De test voor criteriumbereik2, op dezelfde manier geschreven als criterium1.Druk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingKolom F, 5 cellenDruk op Escape om te sluitenGeef je de twee criteria alle vijf klassen tegelijk, dan geeft SOMMEN.ALS vijf totalen terug, één per klasse, in de volgorde van de klassentabel:
- 9.350 (nog niet vervallen)
- 12.000 (1 tot en met 30 dagen)
- 8.500 (31 tot en met 60 dagen)
- 3.250 (61 tot en met 90 dagen)
- 12.300 (meer dan 90 dagen)
In Excel 365 lopen de vijf waarden naar beneden door; dit is de complete ouderdomsanalyse.
=SOM(SOMMEN.ALS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6)) =SOM(SOMMEN.ALS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6)) SOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: getal1Het eerste getal, de eerste cel of het eerste bereik om op te tellen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Openstaand bedrag, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".">=": vergelijkingenDruk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingKolom G, 5 cellenDruk op Escape om te sluitencriteriumbereik2 (optioneel)Bereik voor nog een voorwaarde; tot 127 van zulke paren.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium2 (optioneel)De test voor criteriumbereik2, op dezelfde manier geschreven als criterium1.Druk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingKolom F, 5 cellenDruk op Escape om te sluitenSOM telt de vijf klassetotalen op: 9.350 + 12.000 + 8.500 + 3.250 + 12.300 = 45.400. Dit is het totaal van de ouderdomsanalyse, de hele formule van het voorbeeld.
=SOM(SOMMEN.ALS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))-$F$9 =SOM(SOMMEN.ALS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6))-$F$9 SOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: getal1Het eerste getal, de eerste cel of het eerste bereik om op te tellen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Openstaand bedrag, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".">=": vergelijkingenDruk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingKolom G, 5 cellenDruk op Escape om te sluitencriteriumbereik2 (optioneel)Bereik voor nog een voorwaarde; tot 127 van zulke paren.Kolom Vervaldatum, 8 cellenDruk op Escape om te sluitencriterium2 (optioneel)De test voor criteriumbereik2, op dezelfde manier geschreven als criterium1.Druk op Escape om te sluiten&Voegt tekst samenDruk op Escape om te sluitenVerwijzingInvoercel: 30-09-2026Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingKolom F, 5 cellenDruk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenVerwijzingInvoercel: 45400Druk op Escape om te sluitenDe controle: het totaal van de klassen min het saldo van de controlerekening in
F9(45.400). Het verschil is 0, dus elke factuur staat in precies één klasse en de analyse sluit aan op het grootboek. Elk ander getal wijst op een factuur die in geen enkele klasse of in twee klassen staat.
Stap voor stap
- $F$8-$G$2:$G$6 geeft de vroegste vervaldatum van elke klasse: 30 sep 2026 min 0 = 30 sep 2026, min 30 = 31 aug 2026, min 60 = 1 aug 2026, min 90 = 2 jul 2026 en min 9,9999.999 = 16 mei 1999, een datum zo ver terug dat geen enkele factuur ouder is.
- $F$8-$F$2:$F$6 geeft de laatste vervaldatum van elke klasse: 30 sep 2026 min -999 = 25 jun 2029 (dus geen factuur ligt te ver in de toekomst), min 1 = 29 sep 2026, min 31 = 30 aug 2026, min 61 = 31 jul 2026 en min 91 = 1 jul 2026.
- SOMMEN.ALS telt de openstaande bedragen op van de facturen waarvan de vervaldatum tussen die twee data ligt. Nog niet vervallen (30 sep 2026 tot 25 jun 2029):
26-0501(12 okt) 6,4006.400 +26-0502(2 okt) 2,9502.950 = 9,3509.350. 1 tot en met 30 dagen (31 aug tot 29 sep):26-0503(20 sep) 4,2004.200 +26-0504(2 sep) 7,8007.800 = 12,00012.000. - 31 tot en met 60 dagen (1 aug tot 30 aug): alleen
26-0505(15 aug) = 8,5008.500. 61 tot en met 90 dagen (2 jul tot 31 jul): alleen26-0506(20 jul) = 3,2503.250. - Meer dan 90 dagen (16 mei 1999 tot 1 jul 2026):
26-0507(10 jun) 9,6009.600 +26-0508(25 mei) 2,7002.700 = 12,30012.300. - SOM telt de vijf klassetotalen op: 9,3509.350 + 12,00012.000 + 8,5008.500 + 3,2503.250 + 12,30012.300 = 45,40045.400.
- Het saldo van de controlerekening in
F9is ook 45,40045.400, dus het verschil is 0: alle acht facturen (6,4006.400 + 2,9502.950 + 4,2004.200 + 7,8007.800 + 8,5008.500 + 3,2503.250 + 9,6009.600 + 2,7002.700 = 45,40045.400) staan in precies één klasse.
Waarom het werkt
SOMMEN.ALS accepteert een bereik waar één criterium wordt verwacht en geeft dan één totaal per cel van dat bereik terug. Doordat de klassegrenzen worden omgezet in vervaldatums, beantwoordt één formule de vraag voor elke klasse, en SOM maakt van de klassen weer een totaal. Omdat de klassen in cellen staan, vraagt een ander beleid om een nieuwe tabel en niet om een nieuwe formule, en kun je het totaal met het grootboek vergelijken om aan te tonen dat er geen factuur uit de tabel is gevallen.
Let op
- De controle in
F9laat zo'n factuur zien: zoek elk verschil uit voordat je op de tabel vertrouwt. De klassen moeten zonder gaten of overlap op elkaar aansluiten: 30 en dan 31, niet 30 en dan 40. Een gat verliest facturen, een overlap telt ze dubbel, en in beide gevallen sluit het totaal niet meer aan op de controlerekening.
De buitengrenzen -999 en 9,9999.999 zijn bewust ruim gekozen, zodat geen factuur buiten de eerste of laatste klasse valt.
Vóór Excel 365 heeft SOMMEN.ALS met een bereik als criterium Ctrl+Shift+Enter nodig binnen SOM, of je vult de formule voor één klasse naar beneden aan en telt de cellen op.
De peildatum is een vaste datum in
F8, zodat de tabel hetzelfde blijft als je het dossier later opent; zet er alleen voor een actueel overzicht VANDAAG() neer.
Varianten
=SOMPRODUCT(($F$8-$B$2:$B$9>=$F4)*($F$8-$B$2:$B$9<=$G4)*$C$2:$C$9)
=SOMPRODUCT(($F$8-$B$2:$B$9>=$F4)*($F$8-$B$2:$B$9<=$G4)*$C$2:$C$9)
Dezelfde klasse in oudere Excel-versies, zonder de matrixwerking van SOMMEN.ALS: SOMPRODUCT toetst de dagen over tijd aan de klassegrenzen in rij 4. Voor 15 augustus zijn dat 46 dagen, voor 20 juli 72 dagen enzovoort; alleen 46 ligt tussen 31 en 60, dus het resultaat is 8,5008.500.
=SOMMEN.ALS($C$2:$C$9,$B$2:$B$9, "<"&$F$8)
=SOMMEN.ALS($C$2:$C$9;$B$2:$B$9; "<"&$F$8)
De klassieke snelle variant als je alleen het vervallen deel nodig hebt: alle facturen met een vervaldatum vóór de peildatum. 4,2004.200 + 7,8007.800 + 8,5008.500 + 3,2503.250 + 9,6009.600 + 2,7002.700 = 36,05036.050, dat is 45,40045.400 min de 9,3509.350 die nog niet vervallen is.
Gebruikte functies