Resumé
For at opsummere efter måned i kolonner kan du bruge SUMIFS-funktionen sammen med EOMONTH-funktionen. I det viste eksempel er formlen i G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Denne formel bruger tre navngivne områder: "beløb" (D5: D15), "klient" (B5: B15) og "dato" (C5: C15).
Forklaring
SUMIFS-funktionen er designet til at opsummere værdier i et interval baseret på et eller flere kriterier. I dette tilfælde har vi brug for tre kriterier:
- Klientnavn = klient i kolonne F
- Dato> = første måned (fra dato i række 4)
- Dato <= slut på måned (fra dato i række 4)
Nøglepunkt: månedens navne i G4: I4 er faktisk gyldige datoer, formateret med det brugerdefinerede nummerformat "mmm". Dette giver os mulighed for at bruge datoværdierne i G4: I4 direkte til kriterierne # 2 og # 3 ovenfor.
Tilbage i SUMIFS bruges det første interval / kriteriepar til at kontrollere klientens navn:
client,$F5
Meddelelse F5 er en blandet reference med kolonnen låst. Dette gør det muligt for rækken at ændre sig, da formlen kopieres gennem tabellen, men klientnavnet kommer altid fra kolonne F.
Det andet interval / kriteriepar bruges til at kontrollere datoer mod den første i måneden:
date,">="&G$4
Som nævnt ovenfor er værdierne i G4: I4 faktisk datoer: 1. januar 2019, 1. februar 2019 og 1. marts 2019. Så dette kriterium kontrollerer simpelthen for datoer, der er større end den første i måned i datoer 4. Bemærk dette er en anden blandet reference, denne gang med rækken låst. Dette gør det muligt for kolonnen at ændre sig, når formlen kopieres over hele tabellen, men holder række nummeret fast. Sammenkædningen med et ampersand (&) er nødvendig, når man bygger kriterier, der bruger en logisk operator og en værdi fra en anden celle.
Det tredje interval / kriteriepar bruges til at kontrollere datoer mod den sidste dag i måneden:
date,"<="&EOMONTH(G$4,0)
For at få den sidste dag i hver måned bruger vi EOMONTH-funktionen på datoen fra kolonneoverskriften i række 4. Med nul angivet for månedersargumentet returnerer EOMONTH den sidste dag i samme måned. Igen blandes henvisningen til G4 for at forhindre rækken i at ændre sig.
Pivot Table-løsning
En pivottabel ville være en glimrende løsning på dette problem, fordi den automatisk kan gruppere efter måned uden nogen formler overhovedet. For en side-om-side sammenligning af formler versus pivottabeller, se denne video: Hvorfor pivottabeller.
