Sammendrag
For å oppsummere etter måned i kolonner kan du bruke SUMIFS-funksjonen sammen med EOMONTH-funksjonen. I eksemplet vist er formelen i G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Denne formelen bruker tre navngitte områder: "beløp" (D5: D15), "klient" (B5: B15) og "dato" (C5: C15).
Forklaring
SUMIFS-funksjonen er designet for å summere verdier i et område basert på ett eller flere kriterier. I dette tilfellet trenger vi tre kriterier:
- Klientnavn = klient i kolonne F
- Dato> = første i måneden (fra dato i rad 4)
- Dato <= slutten av måneden (fra dato i rad 4)
Nøkkelpunkt: månedens navn i G4: I4 er faktisk gyldige datoer, formatert med det tilpassede tallformatet "mmm". Dette lar oss bruke datoverdiene i G4: I4 direkte for kriteriene nr. 2 og # 3 ovenfor.
Tilbake i SUMIFS brukes det første intervallet / kriterieparet for å sjekke klientnavnet:
client,$F5
Merknad F5 er en blandet referanse, med kolonnen låst. Dette gjør at raden kan endres etter hvert som formelen kopieres gjennom tabellen, men klientnavnet kommer alltid fra kolonne F.
Det andre området / kriterieparet brukes til å sjekke datoer mot den første i måneden:
date,">="&G$4
Som nevnt ovenfor er verdiene i G4: I4 faktisk datoer: 1. januar 2019, 1. februar 2019 og 1. mars 2019. Så dette kriteriet sjekker bare for datoer som er større enn den første i måneden i rad 4. Legg merke til at dette er en annen blandet referanse, denne gangen med raden låst. Dette gjør at kolonnen kan endres når formelen kopieres over tabellen, men holder radnummeret fast. Sammenkoblingen med et ampersand (&) er nødvendig når du bygger kriterier som bruker en logisk operator og en verdi fra en annen celle.
Det tredje området / kriterieparet brukes til å sjekke datoer mot den siste dagen i måneden:
date,"<="&EOMONTH(G$4,0)
For å få den siste dagen i hver måned, bruker vi EOMONTH-funksjonen på datoen fra kolonneoverskriften i rad 4. Med null angitt for argumentet måneder, returnerer EOMONTH den siste dagen i samme måned. Igjen er referansen til G4 blandet for å forhindre at raden endrer seg.
Pivot Table-løsning
En pivottabell vil være en utmerket løsning for dette problemet, fordi den automatisk kan grupperes etter måned uten formler i det hele tatt. For en sammenligning side om side av formler mot pivottabeller, se denne videoen: Hvorfor pivottabeller.
