Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de SUMIFS-functie kunt gebruiken om gegevens op te tellen die overeenkomen met specifieke maanden in Excel en Google Spreadsheets.
Som indien per maand
Eerst zullen we laten zien hoe u gegevens kunt optellen die overeenkomen met datums die in een specifieke maand en jaar vallen.
We kunnen de SUMIFS-functie gebruiken, samen met de DATE, YEAR, MONTH en EOMONTH-functies om de Aantal verkopen binnen elk Maand.
| 1 | =SOMMEN(C3:C9,B3:B9,">="&DATUM(JAAR(E3),MAAND(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Om de bovenstaande formule te bouwen, beginnen we met het definiëren van het datumbereik voor elke maand. We gebruiken de DATE-functie om de eerste dag van de maand te definiëren (bijv. 1/5/2021). We kunnen dit doen door de datum "hard te coderen" in de formule:
| 1 | ">="&DATUM(2021,5,1) |
Of, beter nog, we kunnen de criteria flexibel maken door te verwijzen naar een datum in cel E3, het jaar en de maand van de datum te extraheren (en de dag gelijk te stellen aan 1), zoals in het bovenstaande voorbeeld:
| 1 | ">="&DATUM(JAAR(E3),MAAND(E3),1) |
Om de laatste dag van de maand te definiëren, kunnen we de EOMONTH-functie gebruiken:
| 1 | "<="&EEN MAAND(E3,0) |
Als we al deze criteria samenvoegen, kunnen we de volgende SUMIFS-formule schrijven:
| 1 | =SOMMEN(C3:C9,B3:B9,">="&DATUM(JAAR(E3),MAAND(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Celverwijzingen vergrendelen
Om onze formules leesbaarder te maken, hebben we de formules weergegeven zonder vergrendelde celverwijzingen:
| 1 | =SOMMEN(C3:C9,B3:B9,">="&DATUM(JAAR(E3),MAAND(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Maar deze formules werken niet goed wanneer ze ergens anders in uw bestand worden gekopieerd en geplakt. Gebruik in plaats daarvan vergrendelde celverwijzingen zoals deze:
| 1 | =SOMMEN($C$3:$C$9,$B$3:$B$9,">="&DATUM(JAAR(E3),MAAND(E3),1),$B$3:$B$9,"<=" &EENMAAND(E3,0)) |
Lees ons artikel over het vergrendelen van celreferenties voor meer informatie.
Maandwaarden opmaken
In dit voorbeeld hebben we maanden weergegeven in kolom E. Deze maandwaarden zijn eigenlijk datums die zijn opgemaakt om de dag weg te laten, met behulp van aangepaste getalnotatie.
Het aangepaste gegevensformaat is "mmm yyyy" om mei 2022 weer te geven.
Som per maand over meerdere jaren
Het bovenstaande voorbeeld somde gegevens op met datums die binnen een specifieke maand en jaar vielen. In plaats daarvan kunt u gegevens optellen met datums die binnen een maand in een jaar vallen met behulp van de SOMPRODUCT-functie.
| 1 | =SOMPRODUCT(C3:C8,--(MAAND(B3:B8)=MAAND(G3))) |
In dit voorbeeld gebruiken we de SOMPRODUCT-functie om gecompliceerde 'som als'-berekeningen uit te voeren. Laten we de formule doornemen.
Dit is onze definitieve formule:
| 1 | =SOMPRODUCT(C3:C8,--(MAAND(B3:B8)=MAAND(G3))) |
Ten eerste vermeldt de SOMPRODUCT-functie de Aantal verkopen voor elk Verkoopdatum en vergelijkt vervolgens de maand van elk Verkoopdatum tegen de opgegeven Maand, waarbij TRUE wordt geretourneerd als de maanden overeenkomen, of FALSE als dit niet het geval is:
| 1 | =SOMPRODUCT({30;42;51;28;17;34},--({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE})) |
Vervolgens zetten de dubbele streepjes (-) de TRUE en FALSE-waarden om in 1s en 0s:
| 1 | =SOMPRODUCT({30;42;51;28;17;34},{1;0;1;0;1;0}) |
De SOMPRODUCT-functie vermenigvuldigt vervolgens elk paar items in de arrays:
| 1 | =SOMPRODUCT({30;0;51;0;17;0}) |
Ten slotte worden de getallen in de array opgeteld:
| 1 | =98 |
Meer details over het gebruik van Booleaanse instructies en de opdracht "-" in een SUMPRODUCT-functie vindt u hier
Som indien per maand in Google Spreadsheets
Deze formules werken in Google Spreadsheets precies hetzelfde als in Excel.
