Denne opplæringen gir en detaljert demonstrasjon av hvordan du bruker SUMIF- og SUMIFS-funksjonene i Google Sheets med formler og eksempler.
SUMIF er en av de matematiske funksjonene i Google Sheets, som brukes til betinget summering av celler. I utgangspunktet ser SUMIF-funksjonen etter en spesifikk tilstand i en rekke celler og legger deretter sammen verdiene som oppfyller den gitte betingelsen.
For eksempel har du en liste over utgifter i Google sheets og du vil kun summere utgiftene som er over en viss maksverdi. Eller du har en liste over bestillingsvarer og deres tilsvarende beløp, og du vil bare vite det totale bestillingsbeløpet for en bestemt vare. Det er her SUMIF-funksjonen kommer godt med.
SUMIF kan brukes til å summere verdier basert på talltilstand, tekstbetingelse, datobetingelse, jokertegn samt basert på tomme og ikke-tomme celler. Google Regneark har to funksjoner for å summere verdier basert på kriterier: SUMIF og SUMIF. SUMIF-funksjonen legger sammen tall basert på én betingelse, mens SUMIFS summerer tall basert på flere forhold.
I denne opplæringen vil vi forklare hvordan du bruker SUMIF- og SUMIFS-funksjonene i Google Sheets for å summere tall som oppfyller en bestemt betingelse.
SUMIF-funksjon i Google Sheets – Syntaks og argumenter
SUM.HVIS-funksjonen er bare en kombinasjon av SOM- og HVIS-funksjonen. HVIS-funksjonen skanner gjennom celleområdet for en gitt tilstand, og deretter summerer SUM-funksjonen tallene som tilsvarer cellene som oppfyller betingelsen.
Syntaks for SUMIF-funksjonen:
Syntaksen til SUMIF-funksjonen i Google Regneark er som følger:
=SUM.HVIS(område; kriterier; [sum_område])Argumenter:
område - Utvalget av celler der vi ser etter cellene som oppfyller kriteriene.
kriterier – Kriteriene som bestemmer hvilke celler som må legges til. Du kan basere kriteriet på tall, tekststreng, dato, cellereferanse, uttrykk, logisk operator, jokertegn samt andre funksjoner.
sum_range – Dette argumentet er valgfritt. Det er dataområdet med verdier som skal summeres hvis den tilsvarende områdeoppføringen samsvarer med betingelsen. Hvis du ikke inkluderer dette argumentet, summeres "området" i stedet.
La oss nå se hvordan du bruker SUMIF-funksjonen til å summere verdier med forskjellige kriterier.
SUMIF-funksjon med tallkriterier
Du kan summere tall som oppfyller visse kriterier i en rekke celler ved å bruke en av følgende sammenligningsoperatorer for å lage kriterier.
- større enn (>)
- mindre enn (<)
- større enn eller lik (>=)
- mindre enn eller lik (<=)
- lik (=)
- ikke lik ()
Anta at du har følgende regneark og du er interessert i det totale salget som er 1000 eller høyere.
Slik kan du gå inn i SUMIF-funksjonen:
Velg først cellen der du vil at utgangen av summen skal vises (D3). For å summere tall i B2:B12 som er større enn eller lik 1000, skriv inn denne formelen og trykk Enter:
=SUM.HVIS(B2:B12;">=1000",B2:B12)I denne eksempelformelen er range- og sum_range-argumentene (B2:B12) de samme, fordi salgstall og kriterier brukes på samme område. Og vi skrev inn tallet før sammenligningsoperatoren og omsluttet det i anførselstegn fordi kriteriene alltid skal være omgitt av doble anførselstegn bortsett fra en cellereferanse.
Formelen så etter tall som er større enn eller lik 1000 og la deretter sammen alle de samsvarende verdiene og viste resultatet i celle D3.
Siden range- og sum_range-argumentene er de samme, kan du oppnå det samme resultatet uten sum_range-argumentene i formelen, slik:
=SUM.HVIS(B2:B12;">=1000")
Eller du kan oppgi cellereferansen (D2) som inneholder nummeret i stedet for tallkriteriene, og slå sammen sammenligningsoperatøren med den cellereferansen i kriterieargumentet:
=SUM.HVIS(B2:B12,">="&D2)Som du kan se, er sammenligningsoperatoren fortsatt angitt i doble anførselstegn, og operatoren og cellereferansen er sammenkoblet med et og-tegn (&). Og du trenger ikke å sette cellereferanse i anførselstegn.
Merk: Når du refererer til cellen som inneholder kriterier, sørg for at du ikke legger igjen noen innledende eller etterfølgende plass i verdien i cellen. Hvis verdien din har unødvendig plass før eller etter verdien i den refererte cellen, vil formelen returnere '0' som et resultat.
Du kan også bruke andre logiske operatorer på samme måte for å lage betingelser i kriterieargumentet. For eksempel, for å summere verdier mindre enn 500:
=SUM.HVIS(B2:B12,"<500")
Sum Hvis tall er lik
Hvis du vil legge til tall som er lik et bestemt tall, kan du enten skrive inn bare tallet eller skrive inn tallet med likhetstegnet i kriterieargumentet.
For for eksempel å summere de tilsvarende salgsbeløpene (kolonne B) for mengder (kolonne C) hvis verdier er lik 20, prøv en av disse formlene:
=SUM.HVIS(C2:C12,"=20",B2:B12)
=SUM.HVIS(C2:C12;20;B2:B12)
=SUM.HVIS(C2:C12;E2;B2:B12)
For å summere tall i kolonne B med mengde som ikke er lik 20 i kolonne C, prøv denne formelen:
=SUM.HVIS(C2:C12;20;B2:B12)
SUMIF-funksjon med tekstkriterier
Hvis du vil legge sammen tall i et celleområde (kolonne eller rad) som tilsvarer cellene som har en bestemt tekst, kan du ganske enkelt inkludere den teksten eller cellen som inneholder teksten i kriterieargumentet til SUMIF-formelen. Vær oppmerksom på at tekststrengen alltid skal være omgitt av doble anførselstegn (" ").
Hvis du for eksempel vil ha det totale salgsbeløpet i 'Vest'-regionen, kan du bruke formelen nedenfor:
=SUM.HVIS(C2:C13,"Vest",B2:B13)I denne formelen søker SUMIF-funksjonen etter verdien 'West' i celleområde C2:C13 og legger sammen tilsvarende salgsverdi i kolonne B. Viser deretter resultatet i celle E3.
Du kan også referere til cellen som inneholder tekst i stedet for å bruke teksten i kriterieargumentet:
=SUM.HVIS(C2:C12;E2;B2:B12)
La oss nå få den totale inntekten for alle regioner unntatt "Vest". For å gjøre det bruker vi ikke lik operatoren () i formelen:
=SUM.HVIS(C2:C12,""&E2;B2:B12)
SUMIF med WildCards
I metoden ovenfor sjekker SUMIF-funksjonen med tekstkriterier området mot den eksakte spesifiserte teksten. Deretter summerer den tallene parrel til nøyaktig tekst og ignorerer alle andre tall inkludert delvis samsvarende tekststreng. For å summere tallene med delvis samsvarende tekststrenger, må du skreddersy ett av følgende jokertegn i kriteriene dine:
?(spørsmålstegn) brukes til å matche et enkelt tegn, hvor som helst i tekststrengen.*(stjerne) brukes til å finne samsvarende ord sammen med en hvilken som helst sekvens av tegn.~(tilde) brukes til å matche tekster med spørsmålstegn (?) eller stjerne (*).
Vi tar dette eksempelregnearket for produkter og deres mengde for å summere tall med jokertegn:
Asterisk (*) Jokertegn
Hvis du for eksempel vil summere mengdene av alle Apple-produkter, bruk denne formelen:
=SUM.HVIS(A2:A14,"Apple*",B2:B14)Denne SUMIF-formelen finner alle produktene med ordet "Apple" i begynnelsen og et hvilket som helst antall tegn etter (angitt med '*'). Når matchen er funnet, oppsummerer den Mengde tall som tilsvarer de samsvarende tekststrengene.
Det er også mulig å bruke flere jokertegn i kriteriene. Og du kan også skrive inn jokertegn med cellereferanser i stedet for direkte tekst.
For å gjøre det må jokertegnene omsluttes av doble anførselstegn(“ ”), og sammenkobles med cellereferansen(e):
=SUM.HVIS(A2:A14,"*"&D2&"*",B2:B14)Denne formelen legger sammen mengdene av alle produktene som har ordet 'Redmi' i seg, uansett hvor ordet er plassert i strengen.
Spørsmålstegn (?) Jokertegn
Du kan bruke jokertegnet for spørsmålstegnet (?) for å matche tekststrenger med enkelttegn.
For eksempel, hvis du vil finne mengder av alle Xiaomi Redmi 9-varianter, kan du bruke denne formelen:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Formelen ovenfor ser etter tekststrenger med ordet "Xiaomi Redmi 9" etterfulgt av enkelttegn og summerer de tilsvarende Mengde tall.
Tilde (~) Jokertegn
Hvis du vil matche et faktisk spørsmålstegn (?) eller stjerne (*), setter du inn tilde-tegnet (~) før jokertegnet i betingelsesdelen av formelen.
For å legge til mengdene i kolonne B med den tilsvarende strengen som har et stjernetegn på slutten, skriv inn formelen nedenfor:
=SUM.HVIS(A2:A14,"Samsung Galaxy V~*",B2:B14)
For å legge til mengder i kolonne B som har et spørsmålstegn (?) i kolonne A i samme rad, prøv formelen nedenfor:
=SUM.HVIS(A2:A14,"~?",B2:B14)
SUMIF-funksjon med datokriterier
SUMIF-funksjonen kan også hjelpe deg med betinget summering av verdier basert på datokriterier – for eksempel tall som tilsvarer en bestemt dato, eller før en dato, eller etter en dato. Du kan også bruke hvilken som helst av sammenligningsoperatorene med en datoverdi for å lage datokriterier for summering av tall.
Datoen må angis i datoformat som støttes av Google Sheets, eller som en cellereferanse som inneholder en dato, eller bruke en datofunksjon som DATE() eller TODAY().
Vi vil bruke dette eksempelregnearket for å vise deg hvordan SUMIF-funksjonen med datokriterier fungerer:
Anta at du vil summere salgsbeløpene som skjedde på eller før (<=) 29. november 2019 i datasettet ovenfor, kan du legge til disse salgstallene ved å bruke SUMIF-funksjonen på en av disse måtene:
=SUM.HVIS(C2:C13,"<=29. november 2019",B2:B13)Formelen ovenfor sjekker hver celle fra C2 til C13 og samsvarer kun med de cellene som inneholder datoer på eller før 29. november 2019 (29.11.2019). Og summerer deretter salgsbeløpet som tilsvarer de samsvarende cellene fra celleområdet B2:B13 og viser resultatet i cellene E3.
Datoen kan gis til formelen i et hvilket som helst format som gjenkjennes av Google Sheets, som "29. november 2019", "29. nov. 2019" eller "29.11.2019", osv. Husk datoverdien og operatøren må alltid stå i doble anførselstegn.
Du kan også bruke funksjonen DATE() i kriteriene i stedet for direkte datoverdi:
=SUM.HVIS(C2:C13,"<="&DATO(2019;11;29);B2:B13)
Eller du kan bruke cellereferanse i stedet for dato i kriteriedelen av formelen:
=SUM.HVIS(C2:C13,"<="&E2;B2:B13)
Hvis du vil legge sammen salgsbeløpene basert på dagens dato, kan du bruke TODAY()-funksjonen i kriterieargumentet.
For eksempel, for å summere alle salgsbeløp for dagens dato, bruk denne formelen:
=SUM.HVIS(C2:C13;I DAG();B2:B13)
SUMIF-funksjon med tomme eller ikke-blanke celler
Noen ganger må du kanskje summere tallene i et celleområde med tomme eller ikke-tomme celler i samme rad. I slike tilfeller kan du bruke SUM.HVIS-funksjonen til å summere verdier basert på kriterier der cellene er tomme eller ikke.
Sum hvis Blank
Det er to kriterier i Google Regneark for å finne tomme celler: «» eller «=».
Hvis du for eksempel vil summere alle salgsbeløpene som inneholder strenger med null lengde (visuelt ser tomme ut) i kolonne C, bruker du doble anførselstegn uten mellomrom i formelen:
=SUM.HVIS(C2:C13,"",B2:B13)
For å summere hele salgsbeløpet i kolonne B med fullstendige tomme celler i kolonne C, inkluderer "=" som kriteriene:
=SUM.HVIS(C2:C13,"=",B2:B13)
Sum hvis ikke tomt:
Hvis du vil summere celler som inneholder en verdi (ikke tomme), kan du bruke "" som kriteriet i formelen:
For eksempel, for å få det totale salgsbeløpet med eventuelle datoer, bruk denne formelen:
=SUM.HVIS(C2:C13,"",B2:B13)
SUMIF Basert på flere kriterier med ELLER-logikk
Som vi har sett så langt er SUMIF-funksjonen designet for å summere tall basert på bare ett enkelt kriterium, men det er mulig å summere verdier basert på flere kriterier med SUMIF-funksjonen i Google Sheets. Det kan gjøres ved å slå sammen mer enn én SUMIF-funksjon i en enkelt formel med OR-logikk.
Hvis du for eksempel ønsker å summere salgsbeløpet i 'Vest'-regionen eller 'Sør'-regionen (ELLER-logikk) i det angitte området (B2:B13), bruk denne formelen:
=SUM.HVIS(C2:C13,"Vest",B2:B13)+SUM.HVIS(C2:C13,"Sør",B2:B13)Denne formelen summerer celler når minst én av betingelsene er SANN. Derfor er det kjent som "ELLER-logikk". Den vil også summere verdier når alle betingelser er oppfylt.
Den første delen av formelen sjekker området C2:C13 for teksten 'Vest' og summerer verdiene i området B2:B13 når samsvaret er oppfylt. Sekunddelen av sjekkene for tekstverdien 'Sør' i samme område C2:C13 og summerer deretter verdier med samsvarende tekst i samme sum_område B2:B13. Deretter legges begge summene sammen og vises i celle E3.
I tilfeller bare ett kriterium er oppfylt, vil det bare returnere den sumverdien.
Du kan også bruke flere kriterier i stedet for bare ett eller to. Og hvis du bruker flere kriterier, er det bedre å bruke en cellereferanse som et kriterium i stedet for å skrive den direkte verdien i formelen.
=SUM.HVIS(C2:C13;E2;B2:B13)+SUM.HVIS(C2:C13;E3;B2:B13)+SUM.HVIS(C2:C13;E4;B2:B13)
SUMIF med OR-logikk legger til verdier når minst ett av de spesifiserte kriteriene er oppfylt, men hvis du bare vil summere verdier når alle de spesifiserte betingelsene er oppfylt, må du bruke den nye søsken-SUMIFS()-funksjonen.
SUMIFS-funksjon i Google Sheets (flere kriterier)
Når du bruker SUM.HVIS-funksjonen til å summere verdier basert på flere kriterier, kan formelen bli for lang og komplisert, og du er tilbøyelig til å gjøre feil. I tillegg vil SUMIF la deg summere verdier kun på et enkelt område og når en av betingelsene er SANN. Det er her SUMIFS-funksjonen kommer inn.
SUMIFS-funksjonen hjelper deg med å summere verdier basert på flere samsvarskriterier i ett eller flere områder. Og den fungerer på OG-logikk, noe som betyr at den bare kan summere verdier når alle de gitte betingelsene er oppfylt. Selv om en betingelse er usann, vil den returnere "0" som et resultat.
SUMIFS funksjonssyntaks og argumenter
Syntaksen til SUMIFS-funksjonen er som følger:
=SUMIFS(sum_område; kriterieområde1; kriterium1; [kriterieområde2; ...], [kriterium2; ...])Hvor,
- sum_range – Området med celler som inneholder verdiene du vil summere når alle betingelser er oppfylt.
- kriterier_område1 – Det er celleområdet du sjekker for kriterier1.
- kriterier1 – Det er tilstanden du må sjekke mot criteria_range1.
- criteria_range2, criterion2, …– De ekstra områdene og kriteriene som skal evalueres. Og du kan legge til flere områder og betingelser til formelen.
Vi bruker datasettet i det følgende skjermbildet for å demonstrere hvordan SUMIFS-funksjonen fungerer med forskjellige kriterier.
SUMIFS med tekstbetingelser
Du kan summere verdier basert på to forskjellige tekstkriterier i forskjellige områder. La oss for eksempel si at du vil finne ut det totale salgsbeløpet for den leverte teltvaren. For dette, bruk denne formelen:
=SUMIFS(D2:D13;A2:A13,"Telt",C2:C13,"Levert")I denne formelen har vi to kriterier: "Telt" og "Leveret". SUMIFS-funksjonen sjekker for varen 'Telt' (criteria1) i området A2:A13 (criteria_range1) og sjekker for statusen 'Delivered' (criteria2) i området C2:C13 (criteria_range2). Når begge betingelsene er oppfylt, summeres den tilsvarende verdien i celleområdet D2:D13 (sum_range).
SUMIFS med tallkriterier og logiske operatører
Du kan bruke betingede operatorer til å lage betingelser med tall for SUMIFS-funksjonen.
For å finne det totale salget av mer enn 5 kvanta av en vare i delstaten California (CA), bruk denne formelen:
=SUMIFS(E2:E13;D2:D13;">5",B2:B13,"CA")Denne formelen har to betingelser: ">5" og "CA".
Denne formelen sjekker for mengder (Antall) større enn 5 i området D2:D13 og sjekker for tilstanden 'CA' i området B2:B13. Og når begge vilkårene er oppfylt (som betyr at det er i samme rad), summeres beløpet i E2:E13.
SUMIFS med datokriterier
SUMIFS-funksjonen lar deg også sjekke flere forhold i samme område så vel som forskjellige områder.
Anta at du vil sjekke det totale salgsbeløpet for de leverte varene etter 31/5/2021 og før 10/6/2021 dato, bruk deretter denne formelen:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Formelen ovenfor har tre betingelser: 31/5/2021, 10/5/2021 og Levert. I stedet for å bruke direkte dato- og tekstverdier, refererte vi til celler som inneholder disse kriteriene.
Formelen sjekker for datoer etter 31/5/2021 (G1) og datoer før 10/6/2021 (G2) i samme område D2:D13, og ser etter statusen «Leveret» mellom disse to datoene. Deretter summerer du det relaterte beløpet i området E2:E13.
SUMIFS med tomme og ikke-blanke celler
Noen ganger vil du kanskje finne summen av verdier når en tilsvarende celle er tom eller ikke. For å gjøre det kan du bruke ett av de tre kriteriene vi diskuterte før: "=", "" og "".
For eksempel, hvis du bare ønsker å summere mengden "Telt" varer som leveringsdatoen ikke er bekreftet ennå (tomme celler), kan du bruke kriteriene "=":
=SUMIFS(D2:D13;A2:A13,"Telt",C2:C13,"=")Formelen ser etter 'Telt'-elementet (criteria1) i kolonne A med tilsvarende tomme celler (criteria2) i kolonne C og summerer deretter tilsvarende beløp i kolonne D. "=" representerer en helt tom celle.
For å finne summen av 'Telt' varer som leveringsdatoen er bekreftet for (ikke tomme celler), bruk "" som et kriterium:
=SUMIFS(D2:D13;A2:A13,"Telt",C2:C13,"")Vi har nettopp byttet "=" for "" i denne formelen. Den finner summen av teltgjenstander med ikke-tomme celler i kolonne C.
SUMIFS med OR Logic
Siden SUMIFS-funksjonen fungerer på OG-logikk, summerer den kun når alle betingelser er oppfylt. Men hva om du vil summere verdi basert på flere kriterier når ett av kriteriene er oppfylt. Trikset er å bruke flere SUMIFS-funksjoner.
Hvis du for eksempel vil legge sammen salgsbeløpet for enten ‘Sykkelstativ’ ELLER ‘Ryggsekk’ når deres status er ‘Bestilt’, prøv denne formelen:
=SUMIFS(D2:D13;A2:A13,"Sykkelstativ",C2:C13,"Bestilt") +SUMIFS(D2:D13;A2:A13,"Ryggsekk",C2:C13,"Bestilt")Den første SUMIFS-funksjonen sjekker to kriterier "Sykkelstativ" og "Bestilt" og summerer beløpsverdiene i kolonne D. Deretter sjekker den andre SUMIFS to kriteriene "Ryggsekk" og "Bestilt" og summerer beløpsverdiene i kolonne D. Og deretter , begge summene legges sammen og vises på F3. Med enkle ord summerer denne formelen når enten ‘Sykkelstativ’ eller ‘ryggsekk’ er bestilt.
Det er alt du trenger å vite om SUMIF- og SUMIFS-funksjonen i Google Sheets.