Dette selvstudie giver en detaljeret demonstration af, hvordan du bruger SUMIF- og SUMIFS-funktionerne i Google Sheets med formler og eksempler.
SUMIF er en af de matematiske funktioner i Google Sheets, som bruges til at betinget summere celler. Grundlæggende leder SUMIF-funktionen efter en specifik tilstand i en række celler og lægger derefter de værdier sammen, der opfylder den givne betingelse.
For eksempel har du en udgiftsliste i Google sheets, og du vil kun opsummere de udgifter, der ligger over en vis max værdi. Eller du har en liste over ordrevarer og deres tilsvarende mængder, og du vil kun kende det samlede ordrebeløb for en bestemt vare. Det er her, SUMIF-funktionen kommer til nytte.
SUMIF kan bruges til at summere værdier baseret på taltilstand, teksttilstand, datotilstand, jokertegn samt baseret på tomme og ikke-tomme celler. Google Sheets har to funktioner til at opsummere værdier baseret på kriterier: SUMIF og SUM. SUMIF-funktionen summerer tal baseret på én betingelse, mens SUMIFS summerer tal baseret på flere forhold.
I denne øvelse vil vi forklare, hvordan du bruger SUMIF- og SUMIFS-funktionerne i Google Sheets til at summere tal, der opfylder en eller flere bestemte betingelser.
SUMIF-funktion i Google Sheets – Syntaks og argumenter
SUM.HVIS-funktionen er blot en kombination af SUM- og HVIS-funktionen. HVIS-funktionen scanner gennem celleområdet for en given betingelse, og derefter summerer SUM-funktionen de tal, der svarer til de celler, der opfylder betingelsen.
Syntaks for SUMIF-funktionen:
Syntaksen for SUMIF-funktionen i Google Sheets er som følger:
=SUM.HVIS(område; kriterier; [sum_område])Argumenter:
rækkevidde - Den række af celler, hvor vi leder efter de celler, der opfylder kriterierne.
kriterier – Kriterierne, der bestemmer, hvilke celler der skal tilføjes. Du kan basere kriteriet på tal, tekststreng, dato, cellereference, udtryk, logisk operator, jokertegn samt andre funktioner.
sum_range – Dette argument er valgfrit. Det er dataområdet med værdier, der skal summeres, hvis den tilsvarende områdeindtastning matcher betingelsen. Hvis du ikke inkluderer dette argument, så summeres 'intervallet' i stedet for.
Lad os nu se, hvordan man bruger SUMIF-funktionen til at summere værdier med forskellige kriterier.
SUMIF-funktion med talkriterier
Du kan summere tal, der opfylder bestemte kriterier, i en række celler ved at bruge en af følgende sammenligningsoperatorer til at lave kriterier.
- større end (>)
- mindre end (<)
- større end eller lig med (>=)
- mindre end eller lig med (<=)
- lig med (=)
- ikke lig med ()
Antag, at du har følgende regneark, og du er interesseret i det samlede salg, der er 1000 eller højere.
Sådan kan du indtaste SUMIF-funktionen:
Først skal du vælge den celle, hvor du ønsker, at outputtet af summen skal vises (D3). For at opsummere tal i B2:B12, der er større end eller lig med 1000, skal du skrive denne formel og trykke på 'Enter':
=SUM.HVIS(B2:B12;">=1000",B2:B12)I dette eksempelformel er interval- og sum_range-argumenterne (B2:B12) de samme, fordi salgstal og -kriterier anvendes på det samme interval. Og vi indtastede tallet før sammenligningsoperatoren og indsatte det i anførselstegn, fordi kriterierne altid skulle være omgivet af dobbelte anførselstegn bortset fra en cellehenvisning.
Formlen ledte efter tal, der er større end eller lig med 1000 og tilføjede derefter alle de matchede værdier og viste resultatet i celle D3.
Da range- og sum_range-argumenterne er de samme, kan du opnå det samme resultat uden sum_range-argumenterne i formlen, sådan her:
=SUM.HVIS(B2:B12;">=1000")
Eller du kan angive cellereferencen (D2), der indeholder nummeret i stedet for talkriterierne, og sammensætte sammenligningsoperatoren med den cellereference i argumentet kriterier:
=SUM.HVIS(B2:B12,">="&D2)Som du kan se, er sammenligningsoperatoren stadig indtastet i dobbelte anførselstegn, og operatoren og cellereferencen er sammenkædet med et og-tegn (&). Og du behøver ikke at sætte cellereference i anførselstegn.
Bemærk: Når du refererer til den celle, der indeholder kriterier, skal du sørge for ikke at efterlade noget indledende eller efterfølgende mellemrum i værdien i cellen. Hvis din værdi har unødvendig plads før eller efter værdien i den refererede celle, vil formlen returnere '0' som et resultat.
Du kan også bruge andre logiske operatorer på samme måde til at lave betingelser i kriterieargumentet. For at summere værdier mindre end 500 f.eks.:
=SUM.HVIS(B2:B12,"<500")
Sum Hvis tal er lig med
Hvis du vil tilføje tal, der er lig med et bestemt tal, kan du enten kun indtaste tallet eller indtaste tallet med lighedstegnet i kriterieargumentet.
For at summere de tilsvarende salgsbeløb (kolonne B) for mængder (kolonne C), hvis værdier er lig med 20, kan du prøve en af disse formler:
=SUM.HVIS(C2:C12,"=20",B2:B12)
=SUM.HVIS(C2:C12;20;B2:B12)
=SUM.HVIS(C2:C12;E2;B2:B12)
For at summere tal i kolonne B med en mængde, der ikke er lig med 20 i kolonne C, prøv denne formel:
=SUM.HVIS(C2:C12;20;B2:B12)
SUMIF-funktion med tekstkriterier
Hvis du vil tilføje tal i et celleområde (kolonne eller række), der svarer til de celler, der har en bestemt tekst, kan du blot inkludere den tekst eller den celle, der indeholder teksten, i kriterieargumentet i din SUMIF-formel. Bemærk venligst, at tekststreng altid skal være omgivet af dobbelte anførselstegn (" ").
For eksempel, hvis du ønsker det samlede salgsbeløb i 'Vest'-regionen, kan du bruge nedenstående formel:
=SUM.HVIS(C2:C13,"Vest",B2:B13)I denne formel søger SUMIF-funktionen efter værdien 'West' i celleområde C2:C13 og summerer tilsvarende salgsværdi i kolonne B. Viser derefter resultatet i celle E3.
Du kan også henvise til cellen, der indeholder tekst i stedet for at bruge teksten i kriterieargumentet:
=SUM.HVIS(C2:C12;E2;B2:B12)
Lad os nu få den samlede omsætning for alle regioner undtagen 'Vest'. For at gøre det bruger vi ikke lig med operatoren () i formlen:
=SUM.HVIS(C2:C12,""&E2;B2:B12)
SUMIF med WildCards
I ovenstående metode kontrollerer SUMIF-funktionen med tekstkriterier intervallet mod den nøjagtige specificerede tekst. Derefter summeres tallene parrel til nøjagtig tekst og ignorerer alle andre tal inklusive delvist matchet tekststreng. For at summere tallene med delvis matchende tekststrenge, skal du skræddersy et af følgende jokertegn i dine kriterier:
?(spørgsmålstegn) bruges til at matche ethvert enkelt tegn, hvor som helst i tekststrengen.*(stjerne) bruges til at finde matchende ord sammen med enhver sekvens af tegn.~(tilde) bruges til at matche tekster med et spørgsmålstegn (?) eller en stjerne (*).
Vi bruger dette eksempel på regneark for produkter og deres mængder for at summere tal med jokertegn:
Asterisk (*) Wildcard
For eksempel, hvis du vil summere mængderne af alle Apple-produkter, skal du bruge denne formel:
=SUM.HVIS(A2:A14,"Apple*",B2:B14)Denne SUMIF-formel finder alle produkter med ordet "Apple" i begyndelsen og et vilkårligt antal tegn efter det (angivet med '*'). Når matchen er fundet, opsummerer den Antal tal, der svarer til de matchende tekststrenge.
Det er også muligt at bruge flere jokertegn i kriterierne. Og du kan også indtaste jokertegn med cellereferencer i stedet for direkte tekst.
For at gøre det skal jokertegnene være omgivet af dobbelte anførselstegn(“ “), og sammenkædet med cellereferencen(erne):
=SUM.HVIS(A2:A14,"*"&D2&"*",B2:B14)Denne formel summerer mængderne af alle de produkter, der har ordet 'Redmi' i sig, uanset hvor ordet er placeret i strengen.
Spørgsmålstegn (?) Wildcard
Du kan bruge spørgsmålstegnet (?) jokertegnet til at matche tekststrenge med ethvert enkelt tegn.
For eksempel, hvis du vil finde mængder af alle Xiaomi Redmi 9-varianter, kan du bruge denne formel:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Ovenstående formel leder efter tekststrenge med ordet "Xiaomi Redmi 9" efterfulgt af enkelte tegn og summerer de tilsvarende Antal tal.
Tilde (~) Wildcard
Hvis du vil matche et faktisk spørgsmålstegn (?) eller stjerne (*), skal du indsætte tilde-tegnet (~) før jokertegnet i formlens betingelsesdel.
For at tilføje mængderne i kolonne B med den tilsvarende streng, der har et stjernetegn i slutningen, skal du indtaste nedenstående formel:
=SUM.HVIS(A2:A14,"Samsung Galaxy V~*",B2:B14)
For at tilføje mængder i kolonne B, der har et spørgsmålstegn (?) i kolonne A i samme række, prøv nedenstående formel:
=SUM.HVIS(A2:A14,"~?",B2:B14)
SUMIF-funktion med datokriterier
SUMIF-funktionen kan også hjælpe dig med at betinget summere værdier baseret på datokriterier - for eksempel tal, der svarer til en bestemt dato, eller før en dato eller efter en dato. Du kan også bruge en hvilken som helst af sammenligningsoperatorerne med en datoværdi til at oprette datokriterier til at summere tal.
Datoen skal indtastes i Google Sheets understøttet datoformat eller som en cellereference, der indeholder en dato, eller ved at bruge en datofunktion såsom DATE() eller TODAY().
Vi vil bruge dette eksempel på regneark til at vise dig, hvordan SUMIF-funktionen med datokriterier fungerer:
Antag, at du vil summere salgsbeløbene, der skete på eller før (<=) 29. november 2019 i ovenstående datasæt, kan du tilføje disse salgstal ved hjælp af SUMIF-funktionen på en af disse måder:
=SUM.HVIS(C2:C13,"<=29. november 2019",B2:B13)Ovenstående formel kontrollerer hver celle fra C2 til C13 og matcher kun de celler, der indeholder datoer på eller før den 29. november 2019 (29/11/2019). Og summerer derefter salgsbeløbet svarende til de matchende celler fra celleområdet B2:B13 og viser resultatet i cellerne E3.
Datoen kan angives til formlen i ethvert format, der genkendes af Google Sheets, såsom '29. november 2019', '29. nov. 2019' eller '29/11/2019' osv. Husk datoværdi, og operatøren skal altid være omgivet af dobbelte anførselstegn.
Du kan også bruge DATE()-funktionen i kriterierne i stedet for direkte datoværdi:
=SUM.HVIS(C2:C13,"<="&DATE(2019;11;29);B2:B13)
Eller du kan bruge cellereference i stedet for dato i kriteriedelen af formlen:
=SUM.HVIS(C2:C13,"<="&E2;B2:B13)
Hvis du vil lægge salgsbeløbene sammen baseret på dagens dato, kan du bruge funktionen TODAY() i argumentet kriterie.
Hvis du f.eks. vil summere alle salgsbeløb for dagens dato, skal du bruge denne formel:
=SUM.HVIS(C2:C13;I DAG();B2:B13)
SUMIF-funktion med tomme eller ikke-blanke celler
Nogle gange skal du muligvis summere tallene i et celleområde med tomme eller ikke-tomme celler i samme række. I sådanne tilfælde kan du bruge SUM.HVIS-funktionen til at summere værdier baseret på kriterier, hvor celler er tomme eller ej.
Sum hvis Blank
Der er to kriterier i Google Sheets for at finde tomme celler: "" eller "=".
For eksempel, hvis du vil summere hele salgsbeløbet, der indeholder nul-længde strenge (visuelt ser tomme ud) i kolonne C, skal du bruge dobbelte anførselstegn uden mellemrum i formlen:
=SUM.HVIS(C2:C13,"",B2:B13)
For at summere hele salgsbeløbet i kolonne B med komplette tomme celler i kolonne C, skal du inkludere "=" som kriteriet:
=SUM.HVIS(C2:C13,"=",B2:B13)
Sum, hvis ikke tomt:
Hvis du vil summere celler, der indeholder en hvilken som helst værdi (ikke tomme), kan du bruge "" som kriteriet i formlen:
For at få det samlede salgsbeløb med eventuelle datoer, skal du f.eks. bruge denne formel:
=SUM.HVIS(C2:C13,"",B2:B13)
SUMIF Baseret på flere kriterier med OR-logik
Som vi har set hidtil er SUMIF-funktionen designet til at summere tal baseret på kun et enkelt kriterium, men det er muligt at summere værdier baseret på flere kriterier med SUMIF-funktionen i Google Sheets. Det kan gøres ved at forbinde mere end én SUMIF-funktion i en enkelt formel med OR-logik.
For eksempel, hvis du vil opsummere salgsbeløbet i 'Vest'-regionen eller 'Syd'-regionen (ELLER-logik) i det angivne interval (B2:B13), skal du bruge denne formel:
=SUM.HVIS(C2:C13,"Vest",B2:B13)+SUM.HVIS(C2:C13,"Syd",B2:B13)Denne formel summerer celler, når mindst én af betingelserne er SAND. Derfor er det kendt som 'ELLER-logik'. Det vil også summere værdier, når alle betingelser er opfyldt.
Den første del af formlen tjekker området C2:C13 for teksten 'Vest' og summerer værdierne i området B2:B13, når matchningen er opfyldt. Sekunddelen af kontrollerne for tekstværdien 'Syd' i samme område C2:C13 og summerer derefter værdier med den matchende tekst i samme sum_område B2:B13. Derefter lægges begge summer sammen og vises i celle E3.
I tilfælde, hvor kun ét kriterium er opfyldt, vil det kun returnere denne sumværdi.
Du kan også bruge flere kriterier i stedet for blot et eller to. Og hvis du bruger flere kriterier, er det bedre at bruge en cellereference som et kriterium i stedet for at skrive den direkte værdi i formlen.
=SUM.HVIS(C2:C13;E2;B2:B13)+SUM.HVIS(C2:C13;E3;B2:B13)+SUM.HVIS(C2:C13;E4;B2:B13)
SUMIF med OR-logik tilføjer værdier, når mindst et af de angivne kriterier er opfyldt, men hvis du kun vil summere værdier, når alle de angivne betingelser er opfyldt, skal du bruge dens nye søskende SUMIFS()-funktion.
SUMIFS-funktion i Google Sheets (flere kriterier)
Når du bruger SUM.HVIS-funktionen til at summere værdier baseret på flere kriterier, kan formlen blive for lang og kompliceret, og du er tilbøjelig til at lave fejl. Derudover vil SUMIF kun lade dig summere værdier på et enkelt område, og når en af betingelserne er SAND. Det er her SUMIFS-funktionen kommer ind.
SUMIFS-funktionen hjælper dig med at summere værdier baseret på flere matchende kriterier i et eller flere områder. Og det virker på OG-logik, hvilket betyder, at det kun kan summere værdier, når alle de givne betingelser er opfyldt. Selvom en betingelse er falsk, vil den returnere '0' som et resultat.
SUMIFS-funktionssyntaks og -argumenter
Syntaksen for SUMIFS-funktionen er som følger:
=SUMIFS(sum_område; kriterium_område1; kriterium1; [kriterieområde2; ...], [kriterium2; ...])Hvor,
- sum_range – Området af celler, der indeholder de værdier, du vil summere, når alle betingelser er opfyldt.
- kriterier_område1 – Det er intervallet af celler, hvor du tjekker for kriterier1.
- kriterier1 – Det er betingelsen, som du skal kontrollere i forhold til criteria_range1.
- criteria_range2, criterion2, …– De yderligere intervaller og kriterier, der skal evalueres. Og du kan tilføje flere områder og betingelser til formlen.
Vi bruger datasættet i det følgende skærmbillede til at demonstrere, hvordan SUMIFS-funktionen fungerer med forskellige kriterier.
SUMIFS med tekstbetingelser
Du kan summere værdier baseret på to forskellige tekstkriterier i forskellige intervaller. Lad os f.eks. sige, at du vil finde ud af det samlede salgsbeløb for den leverede teltvare. Brug denne formel til dette:
=SUMIFS(D2:D13;A2:A13,"Telt",C2:C13,"Leveret")I denne formel har vi to kriterier: "Telt" og "Leveret". SUMIFS-funktionen tjekker for varen 'Tent' (criteria1) i området A2:A13 (criteria_range1) og tjekker for status 'Delivered' (criteria2) i området C2:C13 (criteria_range2). Når begge betingelser er opfyldt, summeres den tilsvarende værdi i celleområdet D2:D13 (sum_range).
SUMIFS med talkriterier og logiske operatører
Du kan bruge betingede operatorer til at oprette betingelser med numre til SUMIFS-funktionen.
For at finde det samlede salg af mere end 5 mængder af en vare i staten Californien (CA), skal du bruge denne formel:
=SUMIFS(E2:E13;D2:D13;">5",B2:B13,"CA")Denne formel har to betingelser: ">5" og "CA".
Denne formel kontrollerer for mængder (Antal) større end 5 i området D2:D13 og kontrollerer for tilstanden 'CA' i området B2:B13. Og når begge betingelser er opfyldt (hvilket betyder, at der er i samme række), summeres beløbet i E2:E13.
SUMIFS med datokriterier
SUMIFS-funktionen giver dig også mulighed for at kontrollere flere forhold i samme område såvel som forskellige områder.
Antag, at du vil kontrollere det samlede salgsbeløb for de leverede varer efter 31/5/2021 og før 10/6/2021 dato, så brug denne formel:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Ovenstående formel har tre betingelser: 31/5/2021, 10/5/2021 og Leveret. I stedet for at bruge direkte dato- og tekstværdier henviste vi til celler, der indeholder disse kriterier.
Formlen tjekker for datoer efter 31/5/2021 (G1) og datoer før 10/6/2021 (G2) i samme interval D2:D13 og tjekker for status 'Leveret' mellem disse to datoer. Opsummerer derefter det relaterede beløb i området E2:E13.
SUMIFS med tomme og ikke-blanke celler
Nogle gange vil du måske finde summen af værdier, når en tilsvarende celle er tom eller ej. For at gøre det kan du bruge et af de tre kriterier, som vi diskuterede før: "=", "" og "".
For eksempel, hvis du kun ønsker at summere mængden af 'Telt'-genstande, for hvilke leveringsdatoen endnu ikke er bekræftet (tomme celler), kan du bruge kriterierne "=":
=SUMIFS(D2:D13;A2:A13,"Telt",C2:C13,"=")Formlen leder efter 'Telt'-elementet (criteria1) i kolonne A med tilsvarende tomme celler (criteria2) i kolonne C og summerer derefter det tilsvarende beløb i kolonne D. "=" repræsenterer en helt tom celle.
For at finde summen af 'Telt' varer, for hvilke leveringsdatoen er blevet bekræftet (ikke tomme celler), skal du bruge "" som et kriterium:
=SUMIFS(D2:D13;A2:A13,"Telt",C2:C13,"")Vi har lige byttet "=" til "" i denne formel. Den finder summen af teltgenstande med ikke-tomme celler i kolonne C.
SUMIFS med OR-logik
Da SUMIFS-funktionen fungerer på OG-logik, summerer den kun, når alle betingelser er opfyldt. Men hvad nu hvis du vil summere værdi baseret på flere kriterier, når et af kriterierne er opfyldt. Tricket er at bruge flere SUMIFS-funktioner.
For eksempel, hvis du vil lægge salgsbeløbet sammen for enten 'Cykelstativ' ELLER 'Rygsæk', når deres status er 'Bestilt', prøv denne formel:
=SUMIFS(D2:D13;A2:A13,"Cykelstativ",C2:C13,"Bestilt") +SUMIFS(D2:D13,A2:A13,"Rygsæk",C2:C13,"Bestilt")Den første SUMIFS-funktion kontrollerer to kriterier "Cykelstativ" og "Bestilt" og summerer beløbsværdierne i kolonne D. Derefter kontrollerer den anden SUMIFS to kriterier "Rygsæk" og "Bestilt" og summerer beløbsværdierne i kolonne D. Og derefter , lægges begge summer sammen og vises på F3. Med enkle ord summerer denne formel, når enten 'Cykelstativ' eller 'rygsæk' er bestilt.
Det er alt, hvad du behøver at vide om SUMIF- og SUMIFS-funktionen i Google Sheets.