Generisk formel
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Sammendrag
For å oppsummere etter måned, kan du bruke en formel basert på SUMIFS-funksjonen, med hjelp fra EOMONTH-funksjonen. I eksemplet vist er formelen i F4:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Denne formelen bruker de nevnte områdene "mengde" (C4: C9) og "dato" (B4: B9).
Forklaring
SUMIFS-funksjonen kan summere verdier i områder basert på flere kriterier.
I dette tilfellet konfigurerer vi SUMIFS til å summere beløp etter måned ved å bruke to kriterier: ett for å matche datoer som er større enn eller lik den første dagen i måneden, en for å matche datoer mindre enn eller lik den siste dagen i måneden. Med andre ord trenger vi en formel som denne:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Eller det mer skuddsikre alternativet:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Denne versjonen er mer robust fordi datoer er konstruert med separate år-, måned- og dagverdier, og ikke blir skrevet inn som tekst som må tolkes av Excel.
Normalt er dette en smerte, for hvis du legger til månedsnavn som tekst (dvs. "januar", "februar", "mars" osv.) I kolonne E, må du gjøre ekstra problemer med å lage datoer du kan bruke til kriterier . I dette tilfellet bruker vi imidlertid et enkelt triks for å gjøre ting enkle: I kolonne E legger vi til faktiske datoer (i stedet for å skrive inn månedsnavn) (1/1/2016, 2/1/2016, 1/3/2016), bruk deretter et tilpasset datoformat ("mmmm") for å vise månedens navn.
Dette gjør det enkelt å bygge kriteriene vi trenger for SUMIFS. For å matche datoer som er større enn eller lik den første i måneden, bruker vi:
">="&E4
Og for å matche datoer som er mindre enn eller lik den siste dagen i måneden, bruker vi dette:
"<="&EOMONTH(E4,0)
Med null i måneder returnerer EOMONTH-funksjonen automatisk den siste dagen i måneden gitt av datoen.
Sammenkoblingen med et ampersand (&) er nødvendig når du bygger kriterier som bruker en logisk operator med en numerisk verdi.
Pivot Table-løsning
En pivottabell er en utmerket løsning når du trenger å oppsummere data etter år, måned, kvartal og så videre, fordi de vil gjøre denne typen gruppering for deg uten noen formler i det hele tatt. For en sammenligning side om side av formler mot pivottabeller, se denne videoen: Hvorfor pivottabeller.
