Denne opplæringen viser hvordan du bruker Eksklel SUMIF og SUMIFS Functioner i Excel og Google Sheets for å summere data som oppfyller visse kriterier.
SUMIF Funksjonsoversikt
Du kan bruke SUMIF -funksjonen i Excel til å summe av celler som inneholder en bestemt verdi, summe celler som er større enn eller lik en verdi, etc.
(Legg merke til hvordan formelinngangene vises)
SUMIF Funksjon Syntaks og argumenter:
| 1 | = SUMIF (område, kriterier, [sum_range]) |
område - Celleområdet du vil bruke kriteriene mot.
kriterier - Kriteriene som brukes for å bestemme hvilke celler som skal legges til.
sum_range - [valgfritt] Cellene som skal legges sammen. Hvis sum_range er utelatt, blir cellene i området lagt sammen i stedet.
Hva er SUMIF -funksjonen?
SUMIF -funksjonen er en av de eldre funksjonene som brukes i regneark. Den brukes til å skanne gjennom en rekke celler som ser etter et bestemt kriterium, og deretter legge til verdier i et område som tilsvarer disse verdiene. Den opprinnelige SUMIF -funksjonen var begrenset til bare ett kriterium. Etter 2007 ble SUMIFS -funksjonen opprettet som tillater en rekke kriterier. Det meste av den generelle bruken er den samme mellom de to, men det er noen kritiske forskjeller i syntaksen som vi vil diskutere gjennom denne artikkelen.
Hvis du ikke allerede har gjort det, kan du gå gjennom mye av den lignende strukturen og eksemplene i COUNTIFS -artikkelen.
Grunnleggende eksempel
La oss vurdere denne listen over registrerte salg, og vi vil vite den totale inntekten.
Fordi vi hadde en utgift, den negative verdien, kan vi ikke bare gjøre en grunnbeløp. I stedet vil vi bare oppsummere verdiene som er større enn 0. “Større enn 0” er det som vil være kriteriene våre i en SUMIF -funksjon. Vår formel for å si dette er
| 1 | = SUMIF (A2: A7, "> 0") |
Eksempel på to kolonner
Mens den opprinnelige SUMIF -funksjonen var designet for å la deg anvende et kriterium på tallene du vil oppsummere, vil du ofte måtte bruke et eller flere kriterier på andre kolonner. La oss vurdere denne tabellen:
Nå, hvis vi bruker den opprinnelige SUMIF -funksjonen til å finne ut hvor mange bananer vi har (oppført i celle D1), må vi gi området vi vil sum som det siste argumentet, og slik ville formelen vår være
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Da programmerere til slutt innså at brukerne ønsket å gi mer enn ett kriterium, ble SUMIFS -funksjonen opprettet. For å lage en struktur som fungerer for et hvilket som helst antall kriterier, krever SUMIFS at sumområdet er oppført først. I vårt eksempel betyr dette at formelen må være
| 1 | = SUMMER (B2: B7, A2: A7, D1) |
MERK: Disse to formlene får samme resultat og kan se like ut, så vær nøye med hvilken funksjon som brukes for å sørge for at du lister opp alle argumentene i riktig rekkefølge.
Arbeider med datoer, flere kriterier
Når du arbeider med datoer i et regneark, mens det er mulig å legge inn datoen direkte i formelen, er det best å ha datoen i en celle, slik at du bare kan referere til cellen i en formel. Dette hjelper for eksempel datamaskinen med å vite at du vil bruke datoen 27.5.2020, og ikke tallet 5 dividert med 27 dividert med 2022.
La oss se på vår neste tabell som registrerer antall besøkende til et nettsted annenhver uke.
Vi kan spesifisere start- og sluttpunktene for området vi vil se på i D2 og E2. Formelen vår for å summere antall besøkende i dette området kan være:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Legg merke til hvordan vi var i stand til å sammenkoble sammenligningene av “=” med cellereferansene for å lage kriteriene. Selv om begge kriteriene ble brukt på det samme celleområdet (A2: A7), må du skrive ut området to ganger, en gang per hvert kriterium.
Flere kolonner
Når du bruker flere kriterier, kan du bruke dem på samme område som vi gjorde med forrige eksempel, eller du kan bruke dem på forskjellige områder. La oss kombinere eksempeldataene våre i denne tabellen:
Vi har konfigurert noen celler for brukeren å skrive inn det de vil søke etter i cellene E2 til G2. Vi trenger dermed en formel som summerer det totale antallet epler plukket i februar. Formelen vår ser slik ut:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS med OR -logikk
Frem til dette punktet har alle eksemplene vi har brukt vært AND OG basert sammenligning, der vi leter etter rader som oppfyller alle våre kriterier. Nå skal vi vurdere saken når du vil søke etter muligheten for at en rad oppfyller et eller annet kriterium.
La oss se på denne salgslisten:
Vi vil legge opp det totale salget for både Adam og Bob. For å gjøre dette har du et par alternativer. Det enkleste er å legge til to SUMIFS sammen, slik:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Her har vi fått datamaskinen til å beregne våre individuelle poengsummer, og deretter legger vi dem sammen.
Vårt neste alternativ er bra når du har flere kriterier, slik at du ikke trenger å skrive om hele formelen gjentatte ganger. I den forrige formelen fortalte vi datamaskinen manuelt å legge til to forskjellige SUMIFS sammen. Du kan imidlertid også gjøre dette ved å skrive kriteriene dine i en matrise, slik:
| 1 | = SUMME (SUMMER (B2: B7, A2: A7, {"Adam", "Bob"})) |
Se på hvordan matrisen er konstruert inne i de krøllete parentesene. Når datamaskinen evaluerer denne formelen, vil den vite at vi ønsker å beregne en SUMIFS -funksjon for hvert element i matrisen vår, og dermed opprette en rekke tall. Den ytre SUM -funksjonen vil deretter ta den rekken med tall og gjøre den til et enkelt tall. Når vi går gjennom formelevalueringen, vil det se slik ut:
| 123 | = SUMME (SUMMER (B2: B7, A2: A7, {"Adam", "Bob"}))= SUMMER (27401, 43470)= 70871 |
Vi får det samme resultatet, men vi klarte å skrive ut formelen litt mer kortfattet.
Å håndtere emner
Noen ganger vil datasettet ditt ha tomme celler som du enten må finne eller unngå. Å sette opp kriteriene for disse kan være litt vanskelig, så la oss se på et annet eksempel.
Vær oppmerksom på at celle A3 virkelig er tom, mens celle A5 har en formel som returnerer en streng med "" null lengde. Hvis vi vil finne totalsummen av virkelig tomme celler, ville vi bruke et kriterium "=", og formelen vår ville se slik ut:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
På den annen side, hvis vi ønsker å få summen for alle celler som visuelt ser tomme ut, endrer vi kriteriene til å være "", og formelen ser ut som
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
La oss snu det: hva om du vil finne summen av ikke-tomme celler? Dessverre vil den nåværende designen ikke la deg unngå null-lengden. Du kan bruke et kriterium på “”, men som du kan se i eksemplet, inneholder det fortsatt verdien fra rad 5.
| 1 | = SUMMER (B2: B7, A2: A7, "") |
Hvis du ikke trenger å telle celler som inneholder strenger med null lengde, vil du vurdere å bruke LEN -funksjonen inne i et SUMPRODUCT
SUMIF i Google Regneark
SUMIF -funksjonen fungerer nøyaktig det samme i Google Regneark som i Excel:
