Generisk formel
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Sammanfattning
För att summera data per månad, ignorera år, kan du använda en formel baserad på funktionerna SUMPRODUCT och MONTH. I exemplet som visas är formeln i H6:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Resultatet är totalt den totala försäljningen i mars, med undantag för året.
Förklaring
Denna datamängd innehåller över 2900 poster, och formeln ovan använder två namngivna intervall:
dates = B5:B2932 amounts = E5:E2932
Inuti SUMPRODUCT-funktionen används MONTH-funktionen för att extrahera månadsnumret för varje datum i datamängden och jämföra det med siffran 3:
(MONTH(dates)=3)
Om vi antar en liten datamängd som listar tre datum vardera i januari, februari och mars (i den ordningen), skulle resultatet bli en matris som innehåller nio nummer så här:
(1;1;1;2;2;2;3;3;3)
där varje nummer är "månadsnummer" för ett datum. När värdena jämförs med 3 är resultatet en matris så här:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Denna matris multipliceras sedan med de beloppsvärden som är associerade med varje marsdatum. Om vi antar att alla nio beloppen är lika med 100 ser operationen ut så här:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Lägg märke till att matematiken ändrar de Sanna falska värdena till ettor och nollor. Efter multiplikation har vi en enda array i SUMPRODUCT:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Observera att de enda kvarvarande beloppen är associerade med mars, resten är noll.
Slutligen returnerar SUMPRODUCT summan av alla objekt - 300 i det förkortade exemplet ovan och 25 521 i skärmdumpen med faktiska data.
Räkna efter månad och ignorera år
För att få ett antal per månad och ignorera år kan du använda SUMPRODUCT så här:
=SUMPRODUCT(--(MONTH(dates)=3))
Genomsnitt per månad som ignorerar år
För att beräkna och medelvärde per månad som ignorerar år kombinerar du de två SUMPRODUCT-formlerna ovan så här:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
