Ladda ner exempelarbetsboken
Denna handledning visar hur du använder SUMIFS -funktionen för att summera data som motsvarar specifika månader i Excel och Google Sheets.
Summa om efter månad
Först kommer vi att visa hur man summerar data som motsvarar datum som faller under en viss månad och år.
Vi kan använda SUMIFS -funktionen tillsammans med DATE, YEAR, MONTH och EOMONTH -funktionerna för att summera Antal försäljningar inom varje Månad.
| 1 | = SUMMER (C3: C9, B3: B9, "> =" & DATUM (ÅR (E3), MÅNAD (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
För att bygga formeln ovan börjar vi med att definiera datumintervallet för varje månad. Vi använder DATE -funktionen för att definiera den första dagen i månaden (ex. 1/5/2021). Vi kan göra detta genom att "hårdkoda" datumet i formeln:
| 1 | "> =" & DATUM (2021,5,1) |
Eller ännu bättre, vi kan göra kriterierna flexibla genom att hänvisa till ett datum i cellen E3, extrahera datumets år och månad (och ställa in dagen lika med 1), som visas i exemplet ovan:
| 1 | "> =" & DATUM (ÅR (E3), MÅNAD (E3), 1) |
För att definiera den sista dagen i månaden kan vi använda EOMONTH -funktionen:
| 1 | "<=" & EOMONTH (E3,0) |
Genom att sätta ihop alla dessa kriterier kan vi skriva följande SUMIFS -formel:
| 1 | = SUMMER (C3: C9, B3: B9, "> =" & DATUM (ÅR (E3), MÅNAD (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Låsning av cellreferenser
För att göra våra formler lättare att läsa har vi visat formlerna utan låsta cellreferenser:
| 1 | = SUMMER (C3: C9, B3: B9, "> =" & DATUM (ÅR (E3), MÅNAD (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Men dessa formler fungerar inte korrekt när de kopieras och klistras in någon annanstans i din fil. Istället bör du använda låsta cellreferenser så här:
| 1 | = SUMIFIER ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATUM (YEAR (E3), MONTH (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Läs vår artikel om låsa cellreferenser för att lära dig mer.
Formatera månadsvärden
I det här exemplet har vi listat månader i kolumn E. Dessa månadsvärden är faktiskt datum som formaterats för att utelämna dagen med hjälp av anpassad nummerformatering.
Det anpassade dataformatet är ”mmm åååå” för att visa maj 2022.
Summa per månad över flera år
Exemplet ovan summerade data med datum som föll inom en viss månad och år. Istället kan du summera data med datum som faller inom en månad varje år med hjälp av SUMPRODUCT -funktionen.
| 1 | = SUMPRODUCT (C3: C8,-(MÅNAD (B3: B8) = MÅNAD (G3))) |
I det här exemplet använder vi SUMPRODUCT -funktionen för att utföra komplicerade "summa om" -beräkningar. Låt oss gå igenom formeln.
Detta är vår sista formel:
| 1 | = SUMPRODUCT (C3: C8,-(MÅNAD (B3: B8) = MÅNAD (G3))) |
Först listar SUMPRODUCT -funktionen Antal försäljningar för varje Försäljningsdatum och jämför sedan månaden för varje Försäljningsdatum mot det angivna Månad, returnerar TRUE om månaderna matchar, eller FALSE om de inte gör det:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Därefter konverterar de dubbla streckarna (-) värdena TRUE och FALSE till 1s och 0s:
| 1 | = SUMPRODUKT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
SUMPRODUCT -funktionen multiplicerar sedan varje par poster i matriserna:
| 1 | = SUMPRODUKT ({30; 0; 51; 0; 17; 0}) |
Slutligen summeras siffrorna i matrisen:
| 1 | =98 |
Mer information om hur du använder booleska uttalanden och kommandot “-” i en SUMPRODUCT-funktion hittar du här
Summa om per månad i Google Kalkylark
Dessa formler fungerar exakt likadant i Google Kalkylark som i Excel.
