Combinatie

Omzet per maand voor twaalf maanden in één formule

Totaal omzet voor elk van twaalf maanden met één SOMMEN.ALS: REEKS telt de maanden, ZELFDE.DAG maakt van elk getal de eerste dag van een maand, en die datums vormen de criteria.

Gebruikte functies:SOMMEN.ALSZELFDE.DAGREEKSExcel 2021 of nieuwer

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Omzet per maand voor de cijferanalyse

Situatie

Voor de cijferanalyse van Veldhuis Installatie BV heb je acht omzetboekingen op rekening 8000 uit de XAF-auditfile over 2025. Je wilt de omzet van elk van de twaalf maanden in één kolom zien, om te vergelijken met de begroting en maanden zonder omzet op te merken, zonder twaalf keer SOMMEN.ALS te schrijven.

De gegevens

ABCDEFG
1DatumBoekstukOmzetEerste maand01-01-2025
215-01-202525-0101€12,500.00€ 12.500,00Omzet per maand€20,900.00€ 20.900,00=SOMMEN.ALS(C2:C9, A2:A9, ">="&ZELFDE.DAG(F1, REEKS(12, 1, 0)), A2:A9, "<"&ZELFDE.DAG(F1, REEKS(12, 1, 1)))=SOMMEN.ALS(C2:C9; A2:A9; ">="&ZELFDE.DAG(F1; REEKS(12; 1; 0)); A2:A9; "<"&ZELFDE.DAG(F1; REEKS(12; 1; 1)))
328-01-202525-0118€8,400.00€ 8.400,00€15,200.00€ 15.200,00
410-02-202525-0204€15,200.00€ 15.200,00€0.00€ 0,00
503-04-202525-0407€9,800.00€ 9.800,00€16,100.00€ 16.100,00
630-04-202525-0433€6,300.00€ 6.300,00€0.00€ 0,00
721-07-202525-0712€21,000.00€ 21.000,00€0.00€ 0,00
814-11-202525-1109€17,500.00€ 17.500,00€21,000.00€ 21.000,00
931-12-202525-1243€11,400.00€ 11.400,00€0.00€ 0,00
10€0.00€ 0,00
11€0.00€ 0,00
12€17,500.00€ 17.500,00
13€11,400.00€ 11.400,00
14
Formulecel (F2)InvoerOverloopresultaat

Formule in F2

=SOMMEN.ALS(C2:C9, A2:A9, ">="&ZELFDE.DAG(F1, REEKS(12, 1, 0)), A2:A9, "<"&ZELFDE.DAG(F1, REEKS(12, 1, 1)))
=SOMMEN.ALS(C2:C9; A2:A9; ">="&ZELFDE.DAG(F1; REEKS(12; 1; 0)); A2:A9; "<"&ZELFDE.DAG(F1; REEKS(12; 1; 1)))

Resultaat: €20,900.00 / €15,200.00 / €0.00 / €16,100.00 / €0.00 / €0.00 / €21,000.00 / €0.00 / €0.00 / €0.00 / €17,500.00 / €11,400.00€ 20.900,00 / € 15.200,00 / € 0,00 / € 16.100,00 / € 0,00 / € 0,00 / € 21.000,00 / € 0,00 / € 0,00 / € 0,00 / € 17.500,00 / € 11.400,00

Hoe de onderdelen samenwerken

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

  1. =REEKS(12, 1, 0)
    =REEKS(12; 1; 0)

    Een kolom van twaalf getallen die bij 0 begint en telkens 1 omhoog gaat: 0, 1, 2, tot en met 11. Dit zijn de maanden die je vanaf de startdatum opschuift.

  2. =ZELFDE.DAG(F1, REEKS(12, 1, 0))
    =ZELFDE.DAG(F1; REEKS(12; 1; 0))

    ZELFDE.DAG schuift de startdatum in F1 (1 januari 2025) op met elk van de twaalf getallen. Het resultaat is twaalf datums: 1 januari 2025, 1 februari 2025, tot en met 1 december 2025. Dit zijn de eerste dagen van de maanden.

  3. =ZELFDE.DAG(F1, REEKS(12, 1, 1))
    =ZELFDE.DAG(F1; REEKS(12; 1; 1))

    Hetzelfde, maar beginnend bij 1 in plaats van 0: 1 februari 2025 tot en met 1 januari 2026. Elke datum is de eerste dag van de volgende maand, dus de bovengrens van de maand ervoor, die zelf niet meetelt.

  4. =">="&ZELFDE.DAG(F1, REEKS(12, 1, 0))
    =">="&ZELFDE.DAG(F1; REEKS(12; 1; 0))

    Zet de operator voor elke datum, zodat je twaalf criteria krijgt. Excel plakt de operator aan het serienummer van de datum, dus de eerste is >=45658 (1 januari 2025). SOMMEN.ALS leest dit als: op of na die datum.

  5. =SOMMEN.ALS(C2:C9, A2:A9, ">="&ZELFDE.DAG(F1, REEKS(12, 1, 0)), A2:A9, "<"&ZELFDE.DAG(F1, REEKS(12, 1, 1)))
    =SOMMEN.ALS(C2:C9; A2:A9; ">="&ZELFDE.DAG(F1; REEKS(12; 1; 0)); A2:A9; "<"&ZELFDE.DAG(F1; REEKS(12; 1; 1)))

    SOMMEN.ALS rekent één keer per maand: het telt de bedragen op met een datum op of na de eerste dag van de maand en vóór de eerste dag van de volgende. Het resultaat is een kolom van twaalf totalen, van 20.900 voor januari tot 11.400 voor december.

Stap voor stap

  1. REEKS(12; 1; 0) geeft 0, 1, 2, tot en met 11. REEKS(12; 1; 1) geeft 1, 2, 3, tot en met 12.
  2. ZELFDE.DAG(F1; 0 tot en met 11) maakt van de startdatum 1 januari 2025 de ondergrenzen: 1 jan, 1 feb, 1 mrt, tot en met 1 dec 2025. ZELFDE.DAG(F1; 1 tot en met 12) geeft de bovengrenzen: 1 feb 2025 tot en met 1 jan 2026.
  3. De operatoren worden aan de datums geplakt, zodat SOMMEN.ALS twaalf paar criteria krijgt: >=1 jan en <1 feb, daarna >=1 feb en <1 mrt, enzovoort.
  4. Januari: 15 en 28 januari tellen mee, dus 12,50012.500 + 8,4008.400 = 20,90020.900. Februari: alleen 10 februari, dus 15,20015.200. Maart: geen boekingen, dus 0.
  5. April: 3 en 30 april, dus 9,8009.800 + 6,3006.300 = 16,10016.100. Mei en juni: 0. Juli: 21,00021.000. Augustus, september en oktober: 0.
  6. November: 17,50017.500. December: de boeking van 31 december ligt vóór 1 januari 2026, dus die telt mee: 11,40011.400. De twaalf totalen tellen op tot 102,100102.100, evenveel als de acht boekingen: 12,50012.500 + 8,4008.400 + 15,20015.200 + 9,8009.800 + 6,3006.300 + 21,00021.000 + 17,50017.500 + 11,40011.400.

Waarom het werkt

SOMMEN.ALS accepteert een matrix als criterium en geeft dan één resultaat per element.

REEKS levert de maandnummers, ZELFDE.DAG zet ze om naar datums en regelt zelf de verschillende maandlengtes en de jaarwisseling, en de ondergrens (inbegrepen) en bovengrens (niet inbegrepen) knippen het jaar in twaalf stukken zonder gaten of overlap. Pas je de startdatum in F1 aan, dan volgen alle twaalf maanden.

Let op

  • De startdatum moet de eerste dag van een maand zijn. Staat in F1 15 januari, dan loopt elk stuk van de 15e tot de 15e en komen de totalen niet meer overeen met de kalendermaanden.
  • Zijn de datums in kolom A tekst (vaak na een CSV-import), dan voldoet geen enkele boeking aan de criteria en tonen alle twaalf totalen 0, zonder waarschuwing. Controleer of A2 rechts is uitgelijnd of ISGETAL(A2) WAAR geeft, en vergelijk de som van de twaalf maanden met het totaal van kolom C.
  • Boekingen buiten de twaalf maanden blijven stilzwijgend buiten beeld. Vergelijk daarom altijd het totaal van de twaalf maanden met het totaal van de rekening, zodat een boeking uit 2024 of 2026 niet onopgemerkt blijft.

Varianten

=SOMMEN.ALS(C2:C9, A2:A9, ">="&F1, A2:A9, "<"&ZELFDE.DAG(F1, 1))
=SOMMEN.ALS(C2:C9; A2:A9; ">="&F1; A2:A9; "<"&ZELFDE.DAG(F1; 1))

De oudere aanpak voor één maand: dezelfde twee criteria zonder matrices, die je twaalf keer naar beneden kopieert met een oplopende startdatum. Geeft 20,90020.900 voor januari.

=SOMMEN.ALS(C2:C9, A2:A9, ">="&F1, A2:A9, "<="&LAATSTE.DAG(F1, 0))
=SOMMEN.ALS(C2:C9; A2:A9; ">="&F1; A2:A9; "<="&LAATSTE.DAG(F1; 0))

De bovengrens als laatste dag van de maand met LAATSTE.DAG. Geeft ook 20,90020.900 voor januari, maar mist boekingen met een tijdstip na middernacht op de laatste dag, dus de versie met de eerste dag van de volgende maand is veiliger.

Alle combinaties