Generisk formel
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Sammanfattning
För att summera efter månad kan du använda en formel baserad på SUMIFS-funktionen, med hjälp av EOMONTH-funktionen. I exemplet som visas är formeln i F4:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Denna formel använder de angivna intervallen "mängd" (C4: C9) och "datum" (B4: B9).
Förklaring
SUMIFS-funktionen kan summera värden i intervall baserat på flera kriterier.
I det här fallet konfigurerar vi SUMIFS att summera belopp per månad med två kriterier: en för att matcha datum som är större än eller lika med den första dagen i månaden, en för att matcha datum som är mindre än eller lika med den sista dagen i månaden. Med andra ord behöver vi en sådan formel:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Eller det mer skottsäkra alternativet:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Denna version är mer robust eftersom datum är konstruerade med separata värden för år, månad och dag och inte anges som text som behöver tolkas av Excel.
Normalt är detta en smärta, för om du lägger till månadsnamn som text (dvs. "januari", "februari", "mars", etc.) i kolumn E måste du göra extra problem för att skapa datum som du kan använda för kriterier . I det här fallet använder vi dock ett enkelt knep för att göra det enkelt: I kolumn E lägger vi till faktiska datum (2016-01-01, 2016-01-21, 2016-01-03), i stället för att skriva månadsnamn, använd sedan ett anpassat datumformat ("mmmm") för att visa månadens namn.
Detta gör det enkelt att bygga de kriterier vi behöver för SUMIFS. För att matcha datum som är större än eller lika med den första i månaden använder vi:
">="&E4
Och för att matcha datum som är mindre än eller lika med den sista dagen i månaden använder vi detta:
"<="&EOMONTH(E4,0)
Med noll i månader returnerar EOMONTH-funktionen automatiskt den sista dagen i månaden som anges av datumet.
Sammankopplingen med ett ampersand (&) är nödvändig när man bygger kriterier som använder en logisk operator med ett numeriskt värde.
Pivot Table-lösning
En pivottabell är en utmärkt lösning när du behöver sammanfatta data efter år, månad, kvartal och så vidare, eftersom de kommer att göra den här typen av gruppering åt dig utan några formler alls. För en jämförelse sida vid sida av formler mot pivottabeller, se den här videon: Varför pivottabeller.
