Generieke formule
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Samenvatting
Om per maand op te tellen, kunt u een formule gebruiken die is gebaseerd op de SUMIFS-functie, met behulp van de EOMONTH-functie. In het getoonde voorbeeld is de formule in F4:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Deze formule gebruikt de genoemde bereiken "aantal" (C4: C9) en "datum" (B4: B9).
Uitleg
De SUMIFS-functie kan waarden in bereiken optellen op basis van meerdere criteria.
In dit geval configureren we SUMIFS om bedragen per maand op te tellen met behulp van twee criteria: één om datums te matchen die groter zijn dan of gelijk zijn aan de eerste dag van de maand, één om datums te matchen die kleiner zijn dan of gelijk zijn aan de laatste dag van de maand. Met andere woorden, we hebben een formule als deze nodig:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Of het meer kogelvrije alternatief:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Deze versie is robuuster omdat datums worden opgebouwd met afzonderlijke jaar-, maand- en dagwaarden en niet worden ingevoerd als tekst die door Excel moet worden geïnterpreteerd.
Normaal gesproken is dit vervelend, want als je maandnamen toevoegt als tekst (bijv. "Januari", "februari", "maart", enz.) In kolom E, moet je extra moeite doen om datums te creëren die je voor criteria kunt gebruiken . In dit geval gebruiken we echter een eenvoudige truc om het gemakkelijk te maken: in kolom E, in plaats van maandnamen te typen, voegen we werkelijke datums toe (1/1/2016, 2/1/2016, 3/1/2016), gebruik vervolgens een aangepaste datumnotatie ("mmmm") om de maandnamen weer te geven.
Dit maakt het gemakkelijk om de criteria op te bouwen die we nodig hebben voor SUMIFS. Om datums te matchen die groter zijn dan of gelijk zijn aan de eerste van de maand, gebruiken we:
">="&E4
En om datums te matchen die kleiner zijn dan of gelijk zijn aan de laatste dag van de maand, gebruiken we dit:
"<="&EOMONTH(E4,0)
Met nul voor maanden, retourneert de functie EOMONTH automatisch de laatste dag van de maand, opgegeven door de datum.
De aaneenschakeling met een ampersand (&) is nodig bij het bouwen van criteria die een logische operator met een numerieke waarde gebruiken.
Draaitafel oplossing
Een draaitabel is een uitstekende oplossing wanneer u gegevens moet samenvatten op jaar, maand, kwartaal, enzovoort, omdat ze dit soort groepering voor u zullen doen zonder enige formules. Zie deze video voor een vergelijking van formules versus draaitabellen naast elkaar: Waarom draaitabellen.
