Sammanfattning
För att summera per månad i kolumner kan du använda SUMIFS-funktionen tillsammans med EOMONTH-funktionen. I exemplet som visas är formeln i G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Denna formel använder tre namngivna intervall: "mängd" (D5: D15), "klient" (B5: B15) och "datum" (C5: C15).
Förklaring
SUMIFS-funktionen är utformad för att summera värden i ett intervall baserat på ett eller flera kriterier. I det här fallet behöver vi tre kriterier:
- Klientnamn = klient i kolumn F
- Datum> = första i månaden (från datum i rad 4)
- Datum <= slutet av månaden (från datum i rad 4)
Nyckelpunkt: månadsnamnen i G4: I4 är faktiskt giltiga datum, formaterade med det anpassade nummerformatet "mmm". Detta gör att vi kan använda datumvärdena i G4: I4 direkt för kriterierna # 2 och # 3 ovan.
Tillbaka i SUMIFS används det första intervallet / kriterieparet för att kontrollera klientens namn:
client,$F5
Observera F5 är en blandad referens, med kolumnen låst. Detta gör att raden kan ändras eftersom formeln kopieras genom tabellen, men klientnamnet kommer alltid från kolumn F.
Det andra intervallet / kriterieparet används för att kontrollera datum mot den första i månaden:
date,">="&G$4
Som nämnts ovan är värdena i G4: I4 faktiskt datum: 1 januari 2019, 1 februari 2019 och 1 mars 2019. Så, detta kriterium söker helt enkelt efter datum som är större än de första i månaden i rad 4. Observera att detta är en annan blandad referens, den här gången med raden låst. Detta gör att kolumnen kan ändras när formeln kopieras över tabellen, men håller radnumret fast. Sammankopplingen med ett ampersand (&) är nödvändig när man bygger kriterier som använder en logisk operator och ett värde från en annan cell.
Det tredje intervall / kriterieparet används för att kontrollera datum mot den sista dagen i månaden:
date,"<="&EOMONTH(G$4,0)
För att få den sista dagen i varje månad använder vi EOMONTH-funktionen på datumet från kolumnrubriken i rad 4. Med noll som anges för månadsargumentet returnerar EOMONTH den sista dagen i samma månad. Återigen är referensen till G4 blandad för att förhindra att raden ändras.
Pivot Table-lösning
En pivottabell skulle vara en utmärkt lösning för detta problem, eftersom den automatiskt kan gruppera efter månad 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.
