Vaak willen we sommige waarden per maand berekenen. Zoals hoeveel verkopen er in een bepaalde maand zijn gedaan. Welnu, dit kan eenvoudig worden gedaan met behulp van draaitabellen, maar als u een dynamisch rapport probeert te hebben, kunnen we een SOMPRODUCT- of SUMIFS-formule gebruiken om per maand op te tellen.
Laten we beginnen met de SUMPRODUCT-oplossing.
Hier is de generieke formule om de som per maand in Excel te krijgen
=SOMPRODUCT(som_bereik,--( TEXT(datum_bereik,"MMM")=maand_tekst))
Sum_range : Het is het bereik dat u per maand wilt optellen.
Datumbereik : Het is het datumbereik waar u maandenlang naar zult kijken.
Month_text: Het is de maand in tekstformaat waarvan u de waarden wilt optellen.
Laten we nu een voorbeeld bekijken:
Voorbeeld: somwaarden per maand in Excel
Hier hebben we enige waarde gekoppeld aan datums. Deze data zijn van januari, februari en maart van het jaar 2019.
Zoals je in de bovenstaande afbeelding kunt zien, zijn alle datums van het jaar 2019. Nu hoeven we alleen de waarden in E2:G2 op te tellen in maanden in E1:G1.
Om nu de waarden volgens maanden op te tellen, schrijft u deze formule in E2:
=SOMPRODUCT(B2:B9,--(TEKST(A2:A9,"MMM")=E1)))
Als u het in aangrenzende cellen wilt kopiëren, gebruik dan absolute verwijzingen of benoemde bereiken zoals u in de afbeelding.
Dit geeft ons de exacte som van elke maand.
Hoe het werkt?
Laten we, beginnend van binnenuit, eens kijken naar de TEKST(A2:A9,"MMM") deel. Hier extraheert de TEXT-functie de maand van elke datum in het bereik A2:A9 in tekstformaat in een array. Vertalen naar formule naar =SOMPRODUCT(B2:B9,--({"Jan";"Jan";"Feb";"Jan";"Feb";"Mar";"Jan";"Feb"}=E1) )
Vervolgens, TEKST(A2:A9,"MMM")=E1: Hier wordt elke maand in array vergeleken met tekst in E1. Aangezien E1 "Jan" bevat, wordt elke "Jan" in de array geconverteerd naar TRUE en andere naar FALSE. Dit vertaalt de formule naar =SOMPRODUCT($B$2:$B$9,--{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE})
Volgende --(TEXT(A2:A9,"MMM")=E1), converteert TRUE FALSE naar binaire waarden 1 en 0. De formule vertaalt zich naar =SUMPRODUCT($B$2:$B$9,{1;1;0; 1;0;0;1;0}).
Eindelijk SOMPRODUCT($B$2:$B$9,{1;1;0;1;0;0;1;0}): de functie SOMPRODUCT vermenigvuldigt de corresponderende waarden in $B$2:$B$9 tot array {1;1; 0;1;0;0;1;0} en telt ze op. Daarom krijgen we som per waarde als 20052 in E1.
SOM INDIEN maanden van ander jaar
In het bovenstaande voorbeeld waren alle datums van hetzelfde jaar. Wat als ze uit verschillende jaren waren? De bovenstaande formule zal de waarden per maand optellen, ongeacht het jaar. Zo worden jan. 2018 en jan. 2019 opgeteld, als we bovenstaande formule gebruiken. Wat in de meeste gevallen fout gaat.
Dit zal gebeuren omdat we geen criteria hebben voor het jaar in het bovenstaande voorbeeld. Als we ook jaarcriteria toevoegen, werkt het.
De generieke formule om de som per maand en jaar in Excel te krijgen
=SUMPRODUCT(sum_range,--( TEXT(date_range,"MMM")=month_text),--( TEXT(date_range,"yyyy")=TEXT(year,0)))
Hier hebben we nog een criterium toegevoegd dat het jaar controleert. Al het andere is hetzelfde.
Laten we het bovenstaande voorbeeld oplossen, deze formule in cel E1 schrijven om de som van Jan in het jaar 2017 te krijgen.
=SOMPRODUCT(B2:B9,--(TEKST(A2:A9,"MMM")=E1),--(TEKST(A2:A9,"jjjj")=TEKST(D2,0)))
Gebruik benoemde bereiken of absolute verwijzingen voordat u in onderstaande cellen kopieert. In de afbeelding heb ik benoemde bereiken gebruikt om in de aangrenzende cellen te kopiëren.
Nu kunnen we de som van de waarde ook in maanden en jaren zien.
Hoe werkt het?
Het eerste deel van de formule is hetzelfde als in het vorige voorbeeld. Laten we het extra deel begrijpen dat de jaarcriteria toevoegt.
--(TEXT(A2:A9,"yyyy")=TEXT(D2,0)): TEXT(A2:A9,"yyyy") converteert de datum in A2:A9 als jaren in tekstformaat naar een array. {"2018";"2019";"2017";"2017";"2019";"2017";"2019";"2017"}.
Meestal wordt het jaar in getalformaat geschreven. Om een getal met tekst we te vergelijken, heb ik jaar int-tekst geconverteerd met behulp van TEXT(D2,0). Vervolgens vergeleken we dit tekstjaar met een reeks jaren als TEXT(A2:A9,"yyyy")=TEXT(D2,0). Dit retourneert een array van true-false {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}. Vervolgens hebben we true false omgezet in een getal met -- operator. Dit geeft ons {0;0;1;1;0;1;0;1}.
Dus uiteindelijk wordt de formule vertaald naar =SOMPRODUCT(B2:B9,{1;1;0;1;0;0;1;0},{0;0;1;1;0;1;0;1 }). Waar de eerste array waarden is. De volgende is de overeenkomende maand en de derde is het jaar. Uiteindelijk krijgen we onze som van waarden als 2160.
De SOMMEN-functie gebruiken om per maand op te tellen
Generieke formule
=SUMIFS(sum_range,date_range,”>=” & startdatum, date_range,”<=” & EOMONTH(start_date,0))
Hier,Sum_range : Het is het bereik dat u per maand wilt optellen.
Datumbereik : Het is het datumbereik waar u maandenlang naar zult kijken.
Startdatum : Dit is de startdatum waarvan u wilt optellen. Voor dit voorbeeld is dit de 1e van de opgegeven maand.
Voorbeeld: somwaarden per maand in Excel
Hier hebben we enige waarde gekoppeld aan datums. Deze data zijn van januari, februari en maart van het jaar 2019.
We hoeven deze waarden alleen maar bij hun maand op te tellen. Nu was het gemakkelijk als we maanden en jaren apart hadden. Maar dat zijn ze niet. We kunnen hier geen helpende kolom gebruiken.
Dus om het rapport voor te bereiden, heb ik een rapportformaat opgesteld dat de maand en de som van waarden bevat. In de maandkolom heb ik eigenlijk een startdatum van de maand. Om Alleen de maand te zien, selecteert u de startdatum en drukt u op CTRL+1.
Schrijf in aangepast formaat "mmm".
Nu hebben we onze gegevens klaar. Laten we de waarden per maand optellen.
Schrijf deze formule in E3 om per maand op te tellen.
=SOMMEN.(B3:B10,A3:A10,">="&D3,A3:A10,"<="&EOMONTH(D3,0))
Gebruik absolute verwijzingen of benoemde bereiken voordat u de formule kopieert.
Dus eindelijk hebben we het resultaat.
Dus, hoe het werkt?
Zoals we weten, kan de functie SUMIFS waarden optellen voor meerdere criteria.
In het bovenstaande voorbeeld is het eerste criterium de som van alle waarden in B3:B10 waarbij de datum in A3:A10 groter is dan of gelijk is aan de datum in D3. D3 bevat 1 jan. Dit vertaalt zich ook.
=SOMMEN(B3:B10,A3:A10,">="& "1-jan-2019" ,A3:A10,"<="EOMONTH(D3,0))
Volgende criterium is alleen som als Datum in A3:A10 is kleiner dan of gelijk aan EOMONTH(D3,0). EOMONTH-functie retourneert alleen het serienummer van de laatste datum van de opgegeven maand. Eindelijk vertaalt formule zich ook.
=SOMMEN.(B3:B10,A3:A10,">=1-jan-2019” ,A3:A10,"<=31-jan-2019”)
Daarom krijgen we de som per maand in Excel.
Voordeel van deze methode is dat u de startdatum voor het optellen van de waarden kunt aanpassen.
Als uw datums verschillende jaren hebben, kunt u het beste draaitabellen gebruiken. Met draaitabellen kunt u eenvoudig de gegevens scheiden in jaar-, kwartaal- en maandindeling.
Dus ja jongens, dit is hoe je waarden per maand kunt optellen. Beide manieren hebben hun eigen specialiteiten. Kies op welke manier je wilt.
Als u vragen heeft over dit artikel of andere Excel- en VBA-gerelateerde vragen, staat het opmerkingengedeelte voor u open.
Gerelateerde artikelen:
Hoe de SUMIF-functie in Excel te gebruiken?
SUMIFS met datums in Excel
SUMIF met niet-lege cellen
Hoe de SUMIFS-functie in Excel te gebruiken
SOMMEN met EN-OF logica
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen: Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel: Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken?: Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken?: Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.