Waarden optellen per jaar in Excel

Stel dat u een gegevensreeks hebt met een lijst van datums en bijbehorende verkoopbedragen, en u het totaalbedrag wilt berekenen voor specifieke jaren, zoals weergegeven in de schermafbeelding hieronder. In Excel helpen de standaardfuncties SUMIFS en SUMPRODUCT u snel en eenvoudig bij deze taak. In deze handleiding wordt stap voor stap uitgelegd hoe u deze twee functies gebruikt.

• Waarden optellen per jaar in Excel met de functies SUMIFS en DATUM
• Waarden optellen per jaar in Excel met de functie SUMPRODUCT

Waarden optellen per jaar in Excel met de functies SUMIFS en DATUM

Om de totale waarde op te tellen voor een opgegeven jaar, geeft u de eerste en laatste datum van dat jaar op met de functie DATUM en gebruikt u vervolgens SUMIFS om alle waarden binnen die specifieke datumperiode op te sommen. De algemene syntaxis is:

• sum_range: Het bereik met waarden dat moet worden opgeteld;
• date_range: Het bereik met datums dat moet worden getest op het specifieke jaar;
• jaar: Het specifieke jaar waarvoor u waarden wilt optellen. ()jaar,1,1 geeft de eerste dag van het jaar aan, en jaar,12,31 geeft de laatste dag van het jaar aan.)

Voer deze taak in Excel uit door de onderstaande formule te kopiëren of in een lege cel in te voeren en vervolgens de formule naar andere cellen te slepen om het resultaat te verkrijgen:

Uitleg van deze formule:

=SUMIFS($C$2:$C$12,$B$2:$B$12,„>="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))

• $C$2:$C$12: is het „som_bereik” met de waarden die we willen optellen.
• $B$2:$B$12,„>="&DATUM(E2,1,1): controleert of de datums in bereik B2:B12 groter dan of gelijk zijn aan 01/01/2020 – de eerste dag van het jaar in cel E2.
• $B$2:$B$12,„<="&DATUM(E2,12,31): controleert of de datums in bereik B2:B12 kleiner dan of gelijk zijn aan 31-12-2020 – de laatste dag van het jaar in cel E2.
• SUMIFS($C$2:$C$12,$B$2:$B$12,„>="&DATUM(E2,1,1),$B$2:$B$12,"<="&DATUM(E2,12,31)): Nadat aan de criteria is voldaan, worden de waarden in het bereik C2:C12 opgeteld.

Waarden optellen per jaar in Excel met de functie SUMPRODUCT

Hier is ook een andere eenvoudige formule, gemaakt met de functie SUMPRODUCT, die u van pas kan komen. De algemene syntaxis is:

• date_range: Het bereik met datums dat moet worden getest op het specifieke jaar;
• year: Het specifieke jaar waarop u waarden wilt optellen;
• sum_rangeHet bereik met waarden dat moet worden opgeteld.

Kopieer of voer de volgende formule in een lege cel in en sleep de formule vervolgens naar andere cellen om het resultaat te verkrijgen:

Uitleg van deze formule:

=SUMPRODUCT((JAAR($B$2:$B$12)=E2)*$C$2:$C$12)

• JAAR($B$2:$B$12)=E2: De functie JAAR haalt het jaartal uit elke datumcel in het bereik B2:B12. Deze expressie vergelijkt of het jaar in E2 overeenkomt met het jaar van elke cel in het bereik B2:B12, wat resulteert in een matrix zoals deze: {WAAR;ONWAAR;WAAR;WAAR;ONWAAR;WAAR;WAAR;ONWAAR;ONWAAR;WAAR;ONWAAR}.
• (JAAR($B$2:$B$12)=E2)*$C$2:$C$12={WAAR;ONWAAR;WAAR;WAAR;ONWAAR;WAAR;WAAR;ONWAAR;ONWAAR;WAAR;ONWAAR}*$C$2:$C$12: In deze expressie worden de WAAR- en ONWAAR-waarden automatisch omgezet naar respectievelijk 1-en en 0-en. Vervolgens wordt deze matrix vermenigvuldigd met elk bedrag in het bereik C2:C12, wat resulteert in een nieuwe matrix zoals deze: {347;0;398;430;0;379;412;0;0;420;0}.
• SUMPRODUCT((JAAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): De functie SUMPRODUCT telt uiteindelijk alle getallen in deze matrix op en geeft het resultaat 2386 terug.

Gebruikte gerelateerde functie:

Meer artikelen: