Combinatie

Totaal debiteuren per ouderdomsklasse van 30 dagen

Je zet elke vervaldatum met X.ZOEKEN en overeenkomstmodus -1 om in een klasse uit een klassentabel en laat SOM daarna de openstaande bedragen van één klasse optellen.

Gebruikte functies:X.ZOEKEN SOMExcel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Openstaande debiteuren 31 tot en met 60 dagen over tijd

Situatie

Voor de debiteuren van Veldhuis Installatie BV heb je de openstaande posten uit de ouderdomsanalyse van de klant met hun vervaldatums. Je wilt weten hoeveel er op de peildatum, 30 september 2026 (getypt in H3), 31 tot en met 60 dagen over tijd is, om de voorziening voor oninbare vorderingen te beoordelen.

De klassen staan in een kleine tabel, zodat je ze kunt aanpassen zonder de formule te wijzigen.

De gegevens

Fragment van het werkblad: in H2 staat de formule
Rij	A	B	C	E	G	H
1	Factuur	Vervaldatum	Openstaand bedrag	Klasse vanaf (dagen)	Gekozen klasse	3131
2	26-0301	15-09-2026	€4,200.00€ 4.200,00	11	Totaal	€14,700.00€ 14.700,00=SOM((X.ZOEKEN($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1)C2:C8)=SOM((X.ZOEKEN($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1)C2:C8)
3	26-0302	20-08-2026	€8,500.00€ 8.500,00	3131	Peildatum	30-09-2026
4	26-0303	05-08-2026	€3,250.00€ 3.250,00	6161
5	26-0304	10-10-2026	€6,000.00€ 6.000,00	9191
6	26-0305	15-07-2026	€12,400.00€ 12.400,00
7	26-0306	20-06-2026	€5,800.00€ 5.800,00
8	26-0307	30-08-2026	€2,950.00€ 2.950,00
9

Formulecel (H2)Invoer

Formule in H2

=SOM((X.ZOEKEN($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1)*C2:C8)

=SOM((X.ZOEKEN($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1)*C2:C8)

Resultaat: €14,700.00€ 14.700,00

Hoe de onderdelen samenwerken

Let op: Zonder de 0 als vierde argument geeft een nog niet vervallen factuur #N/B en geeft SOM voor het hele totaal #N/B.

De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.

```
=$H$3-B2:B8
```
```
=$H$3-B2:B8
```
Het aantal dagen na vervaldatum per factuur, waarbij de peildatum in H3 30 september 2026 is. Het resultaat is één kolom van zeven waarden: 15, 41, 56, -10, 77, 102 en 31. De negatieve waarde is een factuur die nog niet vervallen is.
```
=X.ZOEKEN($H$3-B2:B8, E2:E5, E2:E5, 0, -1)
```
```
=X.ZOEKEN($H$3-B2:B8; E2:E5; E2:E5; 0; -1)
```
Overeenkomstmodus -1 geeft de grootste klassegrens terug die kleiner is dan of gelijk aan het aantal dagen. De zeven waarden worden 1, 31, 31, 0, 61, 91 en 31. De -10 vindt geen klassegrens en krijgt de 0 uit het vierde argument, dat staat voor nog niet vervallen.
```
=X.ZOEKEN($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1
```
```
=X.ZOEKEN($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1
```
Vergelijkt elke klasse met de gekozen klasse 31 in H1. Het resultaat is ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR, ONWAAR, WAAR: de facturen 26-0302, 26-0303 en 26-0307 zijn 31 tot en met 60 dagen over tijd.
```
=(X.ZOEKEN($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1)*C2:C8
```
```
=(X.ZOEKEN($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1)*C2:C8
```
Vermenigvuldigen met de openstaande bedragen houdt het bedrag over waar de klasse klopt en geeft elders 0: 0, 8500, 3250, 0, 0, 0 en 2950.
```
=SOM((X.ZOEKEN($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1)*C2:C8)
```
```
=SOM((X.ZOEKEN($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1)*C2:C8)
```
SOM telt de zeven producten op: 8500 + 3250 + 2950 = 14.700, het openstaande bedrag in de klasse 31 tot en met 60 dagen.

Stap voor stap

$H$3-B2:B8 geeft de dagen over tijd, met de peildatum 30 sep in H3: 30 sep min 15 sep = 15, min 20 aug = 41, min 5 aug = 56, min 10 okt = -10, min 15 jul = 77, min 20 jun = 102, min 30 aug = 31.
X.ZOEKEN zoekt elke waarde op in de klassegrenzen 1, 31, 61 en 91 met overeenkomstmodus -1, de grootste grens die niet boven de waarde ligt: 15 geeft 1, 41 geeft 31, 56 geeft 31, 77 geeft 61, 102 geeft 91 en 31 geeft 31 (een exacte overeenkomst).
De waarde -10 ligt onder de eerste klassegrens, dus er wordt niets gevonden en het vierde argument geeft 0. De zeven klassen zijn 1, 31, 31, 0, 61, 91, 31.
Vergelijken met H1 (31) geeft ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR, ONWAAR, WAAR.
Vermenigvuldigen met de bedragen geeft 0, 8500, 3250, 0, 0, 0 en 2950, want WAAR telt als 1 en ONWAAR als 0.
SOM telt ze op: 8500 + 3250 + 2950 = 14,70014.700.

Waarom het werkt

Overeenkomstmodus -1 maakt van een tabel met klassegrenzen een liniaal: elk aantal dagen valt in de klasse waarvan de grens er het dichtst onder ligt. De vergelijking met de gekozen klasse selecteert de facturen en SOM telt alleen die bedragen op. Omdat de klassen in cellen staan, vraagt een ander beleid om een nieuwe tabel en niet om een nieuwe formule.

Let op

De volgorde van de klassegrenzen verandert het resultaat niet, omdat X.ZOEKEN met overeenkomstmodus -1 elke rij nagaat, maar gesorteerde klassen lezen prettiger.
De peildatum staat als vaste datum in H3, dus het totaal verandert niet als je het dossier later opent. Zet alleen voor een actueel overzicht VANDAAG() in H3: dan verschuiven de dagen over tijd, en dus het totaal, elke dag.
Staat een vervaldatum als tekst, dan mislukt de aftrekking of krijg je verkeerde dagen. Controleer dat kolom B echte datums bevat, bijvoorbeeld door de notatie te wijzigen in een getal.

Varianten

=SOMMEN.ALS(C2:C8, B2:B8, "<="&$H$3-31, B2:B8, ">"&$H$3-61)

=SOMMEN.ALS(C2:C8; B2:B8; "<="&$H$3-31; B2:B8; ">"&$H$3-61)

Het klassieke alternatief: SOMMEN.ALS met twee datumcriteria voor één vaste klasse. Vervaldatums tot en met 30 aug en na 31 jul geven ook 14,70014.700, maar de klasse staat dan in de formule zelf.

=SOMPRODUCT(($H$3-B2:B8>=31)*($H$3-B2:B8<=60)*C2:C8)

=SOMPRODUCT(($H$3-B2:B8>=31)*($H$3-B2:B8<=60)*C2:C8)

SOMPRODUCT met twee voorwaarden op de dagen: 41, 56 en 31 voldoen, dus 8500 + 3250 + 2950 = 14,70014.700. Het werkt in oudere Excel-versies, maar je typt de grenzen van de klasse zelf.

Alle combinaties