Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du bruker SUMIFS -funksjonen til å summere data som tilsvarer bestemte måneder i Excel og Google Sheets.
Sum hvis etter måned
Først vil vi demonstrere hvordan vi summerer data som tilsvarer datoer som faller i en bestemt måned og år.
Vi kan bruke SUMIFS -funksjonen, sammen med DATE, YEAR, MONTH og EOMONTH -funksjonene for å summere Antall salg innenfor hver Måned.
| 1 | = SUMMER (C3: C9, B3: B9, "> =" & DATO (ÅR (E3), MÅNED (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
For å bygge formelen ovenfor starter vi med å definere datointervallet for hver måned. Vi bruker DATE -funksjonen til å definere den første dagen i måneden (eks. 1/5/2021). Vi kan gjøre dette ved å "hardkode" datoen i formelen:
| 1 | "> =" & DATO (2021,5,1) |
Eller enda bedre, vi kan gjøre kriteriene fleksible ved å referere til en dato i cellen E3, trekke ut datoen år og måned (og sette dagen lik 1), som vist i eksemplet ovenfor:
| 1 | "> =" & DATO (ÅR (E3), MÅNED (E3), 1) |
For å definere den siste dagen i måneden kan vi bruke EOMONTH -funksjonen:
| 1 | "<=" Og EOMONTH (E3,0) |
Ved å sette sammen alle disse kriteriene kan vi skrive følgende SUMIFS -formel:
| 1 | = SUMMER (C3: C9, B3: B9, "> =" & DATO (ÅR (E3), MÅNED (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Låsing av cellereferanser
For å gjøre formlene våre lettere å lese, har vi vist formlene uten låste cellereferanser:
| 1 | = SUMMER (C3: C9, B3: B9, "> =" & DATO (ÅR (E3), MÅNED (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Men disse formlene fungerer ikke som de skal når de kopieres og limes inn andre steder i filen. I stedet bør du bruke låste cellereferanser slik:
| 1 | = SUMMER ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATO (YEAR (E3), MONTH (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Les artikkelen vår om låsing av cellereferanser for å lære mer.
Formatering av månedsverdier
I dette eksemplet har vi oppført måneder i kolonne E. Disse månedens verdier er faktisk datoer som er formatert for å utelate dagen, ved hjelp av tilpasset nummerformatering.
Det egendefinerte dataformatet er "mmm åååå" for å vise mai 2022.
Sum etter måned over flere år
Eksemplet ovenfor summerte data med datoer som falt innenfor en bestemt måned og år. I stedet kan du summere data med datoer som faller innen en måned i et hvilket som helst år ved å bruke SUMPRODUCT -funksjonen.
| 1 | = SUMPRODUCT (C3: C8,-(MONTH (B3: B8) = MONTH (G3))) |
I dette eksemplet bruker vi SUMPRODUCT -funksjonen til å utføre kompliserte "sum if" -beregninger. La oss gå gjennom formelen.
Dette er vår siste formel:
| 1 | = SUMPRODUCT (C3: C8,-(MONTH (B3: B8) = MONTH (G3))) |
Først viser SUMPRODUCT -funksjonen Antall salg for hver Salgsdato og sammenligner deretter måneden for hver Salgsdato mot det angitte Måned, returnere TRUE hvis månedene stemmer, eller FALSE hvis de ikke gjør det:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Deretter konverterer de doble bindestrekene (-) de Sanne og FALSKE verdiene til 1 og 0:
| 1 | = SUMPRODUKT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
SUMPRODUCT -funksjonen multipliserer deretter hvert oppføringspar i matrisene:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Til slutt summeres tallene i matrisen:
| 1 | =98 |
Flere detaljer om bruk av boolske setninger og kommandoen “-” i en SUMPRODUCT-funksjon finner du her
Sum hvis etter måned i Google Regneark
Disse formlene fungerer nøyaktig det samme i Google Regneark som i Excel.
