Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger SUMIFS -funktionen til at opsummere data, der svarer til bestemte måneder i Excel og Google Sheets.
Sum hvis efter måned
Først vil vi demonstrere, hvordan man summerer data svarende til datoer, der falder i en bestemt måned og år.
Vi kan bruge SUMIFS -funktionen sammen med funktionerne DATE, YEAR, MONTH og EOMONTH til at opsummere Antal salg inden for hver Måned.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
For at bygge ovenstående formel starter vi med at definere datointervallet for hver måned. Vi bruger DATE -funktionen til at definere den første dag i måneden (eks. 1/5/2021). Vi kan gøre dette ved at "hard-coding" datoen i formlen:
| 1 | "> =" & DATO (2021,5,1) |
Eller endnu bedre, vi kan gøre kriterierne fleksible ved at henvise til en dato i cellen E3, udtrække datoen år og måned (og indstille dagen til 1), som vist i eksemplet ovenfor:
| 1 | "> =" & DATO (ÅR (E3), MÅNED (E3), 1) |
For at definere den sidste dag i måneden kan vi bruge funktionen EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Når vi sætter alle disse kriterier sammen, kan vi skrive følgende SUMIFS -formel:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Låsning af cellereferencer
For at gøre vores formler lettere at læse har vi vist formlerne uden låste cellereferencer:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Men disse formler fungerer ikke korrekt, når de kopieres og indsættes andre steder i din fil. I stedet skal du bruge låste cellereferencer som denne:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATO (YEAR (E3), MONTH (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Læs vores artikel om låsning af cellereferencer for at lære mere.
Formatering af månedsværdier
I dette eksempel har vi angivet måneder i kolonne E. Disse månedsværdier er faktisk datoer formateret til at udelade dagen ved hjælp af tilpasset talformatering.
Det tilpassede dataformat er "mmm åååå" for at vise maj 2022.
Sum efter måned over flere år
Ovenstående eksempel opsummerede data med datoer, der faldt inden for en bestemt måned og år. I stedet kan du opsummere data med datoer, der falder inden for en måned i et hvilket som helst år ved hjælp af SUMPRODUCT -funktionen.
| 1 | = SUMPRODUCT (C3: C8,-(MÅNED (B3: B8) = MÅNED (G3))) |
I dette eksempel bruger vi SUMPRODUCT -funktionen til at udføre komplicerede "sum if" -beregninger. Lad os gå igennem formlen.
Dette er vores endelige formel:
| 1 | = SUMPRODUCT (C3: C8,-(MÅNED (B3: B8) = MÅNED (G3))) |
Først viser SUMPRODUCT -funktionen Antal salg for hver Salgsdato og sammenligner derefter hver måned Salgsdato mod det angivne Måned, returnerer SAND, hvis månederne matcher, eller FALSKT, hvis de ikke gør det:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Dernæst konverterer de dobbelte bindestreger (-) værdierne SAND og FALSK til 1'er og 0'er:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
SUMPRODUCT -funktionen multiplicerer derefter hvert par indgange i arrays:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Endelig summeres tallene i arrayet:
| 1 | =98 |
Flere detaljer om brug af boolske udsagn og kommandoen “-” i en SUMPRODUCT-funktion findes her
Sum hvis efter måned i Google Sheets
Disse formler fungerer nøjagtig det samme i Google Sheets som i Excel.
