Generieke formule
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Samenvatting
Als u gegevens per maand wilt optellen, waarbij jaar wordt genegeerd, kunt u een formule gebruiken die is gebaseerd op de functies SOMPRODUCT en MAAND. In het getoonde voorbeeld is de formule in H6:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Het resultaat is een totaal van alle verkopen in maart, jaar buiten beschouwing gelaten.
Uitleg
Deze dataset bevat meer dan 2900 records en de bovenstaande formule gebruikt twee benoemde bereiken:
dates = B5:B2932 amounts = E5:E2932
Binnen de SUMPRODUCT-functie wordt de MAAND-functie gebruikt om het maandnummer voor elke datum in de gegevensset te extraheren en te vergelijken met het getal 3:
(MONTH(dates)=3)
Als we uitgaan van een kleine dataset met elk 3 datums in januari, februari en maart (in die volgorde), zou het resultaat een array zijn met negen getallen zoals deze:
(1;1;1;2;2;2;3;3;3)
waarbij elk nummer het "maandnummer" is voor een datum. Wanneer de waarden worden vergeleken met 3, is het resultaat een array als deze:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Deze matrix wordt vervolgens vermenigvuldigd met de bedragwaarden die bij elke maart-datum horen. Als we aannemen dat alle negen bedragen gelijk zijn aan 100, ziet de operatie er als volgt uit:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Merk op dat de wiskundige bewerking de TRUE FALSE-waarden verandert in enen en nullen. Na vermenigvuldiging hebben we een enkele array in SOMPRODUCT:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Merk op dat de enige overgebleven bedragen zijn gekoppeld aan maart, de rest is nul.
Ten slotte retourneert SOMPRODUCT de som van alle items - 300 in het verkorte voorbeeld hierboven en 25.521 in de schermafbeelding met werkelijke gegevens.
Tel per maand en negeer het jaar
Om een telling per maand te krijgen waarbij het jaar wordt genegeerd, kunt u SOMPRODUCT als volgt gebruiken:
=SUMPRODUCT(--(MONTH(dates)=3))
Gemiddeld per maand zonder jaar
Om het jaar te berekenen en gemiddeld per maand te negeren, combineert u de twee SOMPRODUCT-formules hierboven als volgt:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
