Den här handledningen ger en detaljerad demonstration av hur du använder SUMIF- och SUMIFS-funktionerna i Google Kalkylark med formler och exempel.
SUMIF är en av de matematiska funktionerna i Google Sheets, som används för att villkorligt summera celler. I grund och botten letar SUMIF-funktionen efter ett specifikt villkor i ett cellintervall och lägger sedan ihop de värden som uppfyller det givna villkoret.
Du har till exempel en lista över utgifter i Google sheets och du vill bara summera de utgifter som ligger över ett visst maxvärde. Eller så har du en lista över beställningsartiklar och deras motsvarande belopp, och du vill bara veta det totala beställningsbeloppet för en specifik artikel. Det är där SUMIF-funktionen kommer väl till pass.
SUMIF kan användas för att summera värden baserat på talvillkor, textvillkor, datumvillkor, jokertecken samt baserat på tomma och icke-tomma celler. Google Kalkylark har två funktioner för att summera värden baserat på kriterier: SUMIF och SUMIFS. SUMIF-funktionen summerar siffror baserat på ett villkor medan SUMIFS summerar tal baserat på flera villkor.
I den här självstudien kommer vi att förklara hur du använder funktionerna SUMIF och SUMIFS i Google Kalkylark för att summera tal som uppfyller ett eller flera villkor.
SUMIF-funktion i Google Sheets – Syntax och argument
SUM.OM-funktionen är bara en kombination av SUMMA- och OM-funktionen. OM-funktionen söker igenom cellintervallet för ett givet villkor, och sedan summerar SUM-funktionen siffrorna som motsvarar de celler som uppfyller villkoret.
Syntax för SUMIF-funktionen:
Syntaxen för SUMIF-funktionen i Google Kalkylark är följande:
=SUMMAOM(intervall; kriterium; [summa_intervall])Argument:
utbud – Cellintervallet där vi letar efter de celler som uppfyller kriterierna.
kriterier – Kriterierna som avgör vilka celler som behöver läggas till. Du kan basera kriteriet på nummer, textsträng, datum, cellreferens, uttryck, logisk operator, jokertecken samt andra funktioner.
summa_intervall – Detta argument är valfritt. Det är dataintervallet med värden som ska summeras om motsvarande intervallpost matchar villkoret. Om du inte inkluderar detta argument, summeras "intervallet" istället.
Låt oss nu se hur man använder SUMIF-funktionen för att summera värden med olika kriterier.
SUMIF-funktion med nummerkriterier
Du kan summera tal som uppfyller vissa kriterier i ett cellintervall genom att använda en av följande jämförelseoperatorer för att skapa kriterier.
- större än (>)
- mindre än (<)
- större än eller lika med (>=)
- mindre än eller lika med (<=)
- lika med (=)
- inte lika med ()
Anta att du har följande kalkylblad och du är intresserad av den totala försäljningen som är 1000 eller högre.
Så här kan du ange SUMIF-funktionen:
Välj först cellen där du vill att utmatningen av summan ska visas (D3). För att summera siffror i B2:B12 som är större än eller lika med 1000, skriv den här formeln och tryck på 'Enter':
=SUMMAOM(B2:B12,">=1000",B2:B12)I den här exempelformeln är argumenten range och summa_range (B2:B12) desamma, eftersom försäljningsnummer och kriterier tillämpas på samma område. Och vi skrev in numret före jämförelseoperatorn och satte det inom citattecken eftersom kriterierna alltid ska stå inom dubbla citattecken förutom en cellreferens.
Formeln letade efter tal som är större än eller lika med 1000 och adderade sedan alla matchade värden och visade resultatet i cell D3.
Eftersom range- och summa_range-argumenten är desamma kan du uppnå samma resultat utan summa_range-argumenten i formeln, så här:
=SUMMA.OM(B2:B12,">=1000")
Eller så kan du ange cellreferensen (D2) som innehåller numret istället för sifferkriterierna och slå ihop jämförelseoperatorn med den cellreferensen i argumentet kriterier:
=SUMMA.OM(B2:B12,">="&D2)Som du kan se skrivs jämförelseoperatorn fortfarande in med dubbla citattecken och operatorn och cellreferensen är sammanlänkade med ett et-tecken (&). Och du behöver inte bifoga cellreferens inom citattecken.
Notera: När du hänvisar till cellen som innehåller kriterier, se till att inte lämna något inledande eller efterföljande mellanslag i värdet i cellen. Om ditt värde har något onödigt utrymme före eller efter värdet i den refererade cellen, kommer formeln att returnera "0" som ett resultat.
Du kan också använda andra logiska operatorer på samma sätt för att skapa villkor i kriterieargumentet. Till exempel, för att summera värden mindre än 500:
=SUMMAOM(B2:B12,"<500")
Summa om siffror är lika med
Om du vill lägga till siffror som är lika med ett visst tal kan du antingen ange endast siffran eller ange siffran med likhetstecknet i kriterieargumentet.
För att till exempel summera motsvarande försäljningsbelopp (kolumn B) för kvantiteter (kolumn C) vars värden är lika med 20, prova någon av dessa formler:
=SUMMA.OM(C2:C12,"=20",B2:B12)
=SUMMAOM(C2:C12,"20",B2:B12)
=SUMMAOM(C2:C12;E2;B2:B12)
För att summera siffror i kolumn B med kvantitet som inte är lika med 20 i kolumn C, prova denna formel:
=SUMMAOM(C2:C12,"20",B2:B12)
SUMIF-funktion med textkriterier
Om du vill lägga ihop siffror i ett cellområde (kolumn eller rad) som motsvarar de celler som har en specifik text, kan du helt enkelt inkludera den texten eller cellen som innehåller texten i kriterieargumentet i din SUMIF-formel. Observera att textsträng alltid ska vara omgiven av dubbla citattecken (" ").
Om du till exempel vill ha det totala försäljningsbeloppet i regionen "Västra" kan du använda formeln nedan:
=SUMMAOM(C2:C13,"Väst",B2:B13)I den här formeln söker SUMIF-funktionen efter värdet 'West' i cellintervall C2:C13 och lägger ihop motsvarande försäljningsvärde i kolumn B. Visar sedan resultatet i cell E3.
Du kan också hänvisa till cellen som innehåller text istället för att använda texten i kriterieargumentet:
=SUMMAOM(C2:C12;E2;B2:B12)
Låt oss nu få de totala intäkterna för alla regioner utom "Väst". För att göra det använder vi inte lika med operatorn () i formeln:
=SUMMA.OM(C2:C12,""&E2;B2:B12)
SUMIF med WildCards
I metoden ovan kontrollerar SUMIF-funktionen med textkriterier intervallet mot den exakt specificerade texten. Sedan summerar den siffrorna parrel till exakt text och ignorerar alla andra siffror inklusive delvis matchad textsträng. För att summera siffrorna med delvis matchande textsträngar måste du skräddarsy ett av följande jokertecken i dina kriterier:
?(frågetecken) används för att matcha varje enskilt tecken, var som helst i textsträngen.*(asterisk) används för att hitta matchande ord tillsammans med valfri teckensekvens.~(tilde) används för att matcha texter med ett frågetecken (?) eller asterisk (*).
Vi tar det här exemplet för produkter och deras kvantiteter för att summera siffror med jokertecken:
Asterisk (*) Jokertecken
Om du till exempel vill summera mängderna av alla Apple-produkter, använd den här formeln:
=SUMMAOM(A2:A14,"Apple*",B2:B14)Denna SUMIF-formel hittar alla produkter med ordet "Apple" i början och valfritt antal tecken efter det (betecknas med '*'). När matchningen har hittats sammanfattar den Kvantitet siffror som motsvarar de matchande textsträngarna.
Det är också möjligt att använda flera jokertecken i kriterierna. Och du kan också ange jokertecken med cellreferenser istället för direkttext.
För att göra det måste jokertecken omges av dubbla citattecken (“ ”), och sammanfogas med cellreferenserna:
=SUMMA OM(A2:A14,"*"&D2&"*",B2:B14)Denna formel summerar mängden av alla produkter som har ordet "Redmi" i sig, oavsett var ordet finns i strängen.
Frågetecken (?) Jokertecken
Du kan använda jokertecken från frågetecknet (?) för att matcha textsträngar med enstaka tecken.
Om du till exempel vill hitta mängder av alla Xiaomi Redmi 9-varianter kan du använda den här formeln:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Ovanstående formel letar efter textsträngar med ordet "Xiaomi Redmi 9" följt av enstaka tecken och summerar motsvarande Kvantitet tal.
Tilde (~) Jokertecken
Om du vill matcha ett faktiskt frågetecken (?) eller asterisk (*), infogar du tecknet tilde (~) före jokertecknet i formelns villkorsdel.
För att lägga till kvantiteterna i kolumn B med motsvarande sträng som har ett asterisktecken i slutet, skriv in formeln nedan:
=SUMMA.OM(A2:A14,"Samsung Galaxy V~*",B2:B14)
För att lägga till kvantiteter i kolumn B som har ett frågetecken (?) i kolumn A på samma rad, prova följande formel:
=SUMMA.OM(A2:A14,"~?",B2:B14)
SUMIF-funktion med datumkriterier
SUMIF-funktionen kan också hjälpa dig att villkorligt summera värden baserat på datumkriterier – till exempel siffror som motsvarar ett visst datum, eller före ett datum eller efter ett datum. Du kan också använda någon av jämförelseoperatorerna med ett datumvärde för att skapa datumkriterier för att summera tal.
Datumet måste anges i datumformat som stöds av Google Sheets, eller som en cellreferens som innehåller ett datum, eller med en datumfunktion som DATE() eller TODAY().
Vi kommer att använda det här exemplet för att visa dig hur SUMIF-funktionen med datumkriterier fungerar:
Anta att du vill summera försäljningsbeloppen som hände på eller före (<=) 29 november 2019 i ovanstående datauppsättning, kan du lägga till dessa försäljningssiffror med SUMIF-funktionen på ett av dessa sätt:
=SUMMAOM(C2:C13,"<=29 november 2019",B2:B13)Ovanstående formel kontrollerar varje cell från C2 till C13 och matchar endast de celler som innehåller datum på eller före 29 november 2019 (29/11/2019). Och summerar sedan försäljningsbeloppet som motsvarar de matchande cellerna från cellområdet B2:B13 och visar resultatet i cellerna E3.
Datumet kan anges till formeln i vilket format som helst som känns igen av Google Kalkylark, som "29 november 2019", "29 november 2019" eller "29/11/2019", etc. Kom ihåg datumvärdet och operatorn måste alltid omges av dubbla citattecken.
Du kan också använda DATE()-funktionen i kriterierna istället för direkt datumvärde:
=SUMMA.OM(C2:C13,"<="&DATE(2019;11;29);B2:B13)
Eller så kan du använda cellreferens istället för datum i kriteriedelen av formeln:
=SUMMAOM(C2:C13,"<="&E2,B2:B13)
Om du vill lägga samman försäljningsbeloppen baserat på dagens datum kan du använda funktionen TODAY() i argumentet kriterier.
För att till exempel summera alla försäljningsbelopp för dagens datum, använd den här formeln:
=SUMMA.OM(C2:C13;I DAG();B2:B13)
SUMIF-funktion med tomma eller icke-tomma celler
Ibland kan du behöva summera siffrorna i ett cellintervall med tomma eller icke-tomma celler på samma rad. I sådana fall kan du använda SUM.OM-funktionen för att summera värden baserat på kriterier där celler är tomma eller inte.
Summa om tom
Det finns två kriterier i Google Kalkylark för att hitta tomma celler: "" eller "=".
Om du till exempel vill summera alla försäljningsbelopp som innehåller nolllängda strängar (visuellt ser tomma ut) i kolumn C, använd dubbla citattecken utan mellanslag i formeln:
=SUMMAOM(C2:C13,"",B2:B13)
För att summera alla försäljningsbelopp i kolumn B med fullständiga tomma celler i kolumn C, inkludera "=" som kriteriet:
=SUMMA.OM(C2:C13,"=",B2:B13)
Summa om inte tomt:
Om du vill summera celler som innehåller valfritt värde (inte tomma), kan du använda "" som kriterium i formeln:
Till exempel, för att få det totala försäljningsbeloppet med eventuella datum, använd den här formeln:
=SUMMAOM(C2:C13,"",B2:B13)
SUMIF Baserat på flera kriterier med ELLER-logik
Som vi har sett hittills är SUMIF-funktionen utformad för att summera tal baserat på bara ett enda kriterium, men det är möjligt att summera värden baserat på flera kriterier med SUMIF-funktionen i Google Sheets. Det kan göras genom att sammanfoga mer än en SUMIF-funktion i en enda formel med ELLER-logik.
Om du till exempel vill summera försäljningsbeloppet i regionen "Väst" eller "Södra" (ELLER logik) i det angivna intervallet (B2:B13), använd den här formeln:
=SUMMAOM(C2:C13,"Väst",B2:B13)+SUMMAOM(C2:C13,"Söder",B2:B13)Den här formeln summerar celler när minst ett av villkoren är SANT. Därför är det känt som "ELLER-logik". Den kommer också att summera värden när alla villkor är uppfyllda.
Den första delen av formeln kontrollerar intervallet C2:C13 för texten "Väst" och summerar värdena i intervallet B2:B13 när matchningen är uppfylld. Sekunddelen av kontrollerna efter textvärdet 'Söder' i samma intervall C2:C13 och summerar sedan värden med den matchande texten i samma summa_intervall B2:B13. Sedan läggs båda summorna ihop och visas i cell E3.
I de fall endast ett kriterium är uppfyllt, returnerar det bara det summavärdet.
Du kan också använda flera kriterier istället för bara ett eller två. Och om du använder flera kriterier är det bättre att använda en cellreferens som ett kriterium istället för att skriva det direkta värdet i formeln.
=SUMMAOM(C2:C13;E2;B2:B13)+SUMMAOM(C2:C13;E3;B2:B13)+SUMMAOM(C2:C13;E4,B2:B13)
SUMIF med OR-logik lägger till värden när minst ett av de angivna kriterierna är uppfyllt, men om du bara vill summera värden när alla angivna villkor är uppfyllda måste du använda dess nya syskon SUMIFS()-funktion.
SUMIFS-funktion i Google Kalkylark (flera kriterier)
När du använder SUM.OM-funktionen för att summera värden baserat på flera kriterier, kan formeln bli för lång och komplicerad, och du är benägen att göra misstag. Förutom att SUMIF låter dig summera värden endast på ett enda område och när något av villkoren är SANT. Det är där SUMIFS-funktionen kommer in.
SUMIFS-funktionen hjälper dig att summera värden baserat på flera matchningskriterier i ett eller flera intervall. Och det fungerar på OCH-logik, vilket betyder att det bara kan summera värden endast när alla givna villkor är uppfyllda. Även om ett villkor är falskt kommer det att returnera "0" som ett resultat.
SUMIFS Funktionssyntax och argument
Syntaxen för SUMIFS-funktionen är som följer:
=SUMIFS(summaintervall, kriteriumintervall1, kriterium1, [kriteriumintervall2, ...], [kriterium2, ...])Var,
- summa_range – Cellintervallet som innehåller de värden du vill summera när alla villkor är uppfyllda.
- criteria_range1 – Det är cellintervallet där du söker efter kriterier1.
- kriterier1 – Det är villkoret som du måste kontrollera mot criteria_range1.
- criteria_range2, criterion2, …– De ytterligare intervallen och kriterierna att utvärdera. Och du kan lägga till fler intervall och villkor till formeln.
Vi kommer att använda datamängden i följande skärmdump för att visa hur SUMIFS-funktionen fungerar med olika kriterier.
SUMIFS med textvillkor
Du kan summera värden baserat på två olika textkriterier i olika intervall. Låt oss till exempel säga att du vill ta reda på det totala försäljningsbeloppet för det levererade tältobjektet. Använd denna formel för detta:
=SUMIFS(D2:D13;A2:A13,"Tält",C2:C13,"Leverat")I denna formel har vi två kriterier: "Tält" och "Leverat". SUMIFS-funktionen kontrollerar för artikeln 'Tält' (criteria1) i intervallet A2:A13 (criteria_range1) och kontrollerar efter status 'Delivered' (criteria2) i intervallet C2:C13 (criteria_range2). När båda villkoren är uppfyllda, summerar den motsvarande värde i cellområdet D2:D13 (sum_range).
SUMIFS med nummerkriterier och logiska operatorer
Du kan använda villkorliga operatorer för att skapa villkor med nummer för SUMIFS-funktionen.
För att hitta den totala försäljningen av mer än 5 kvantiteter av en vara i delstaten Kalifornien (CA), använd denna formel:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Denna formel har två villkor: ">5" och "CA".
Den här formeln kontrollerar kvantiteter (Antal) som är större än 5 i intervallet D2:D13 och kontrollerar tillståndet 'CA' i intervallet B2:B13. Och när båda villkoren är uppfyllda (vilket betyder att det finns i samma rad), summerar den summan i E2:E13.
SUMIFS med datumkriterier
SUMIFS-funktionen låter dig också kontrollera flera förhållanden i samma intervall såväl som olika intervall.
Anta att du vill kontrollera det totala försäljningsbeloppet för de levererade varorna efter 31/5/2021 och före 10/6/2021 datum, använd sedan denna formel:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Ovanstående formel har tre villkor: 31/5/2021, 10/5/2021 och Levererat. Istället för att använda direkta datum- och textvärden hänvisade vi till celler som innehåller dessa kriterier.
Formeln söker efter datum efter 31/5/2021 (G1) och datum före 10/6/2021 (G2) i samma intervall D2:D13, och kontrollerar statusen "Leverat" mellan dessa två datum. Summerar sedan det relaterade beloppet i intervallet E2:E13.
SUMIFS med tomma och icke-tomma celler
Ibland kanske du vill hitta summan av värden när en motsvarande cell är tom eller inte. För att göra det kan du använda ett av de tre kriterierna som vi diskuterade tidigare: "=", "" och "".
Om du till exempel bara vill summera mängden "Tält" för vilka leveransdatumet inte har bekräftats ännu (tomma celler), kan du använda kriterierna "=":
=SUMIFS(D2:D13,A2:A13,"Tält",C2:C13,"=")Formeln letar efter "Tält"-objektet (criteria1) i kolumn A med motsvarande tomma celler (criteria2) i kolumn C och summerar sedan motsvarande belopp i kolumn D. "=" representerar en helt tom cell.
För att hitta summan av "Tält"-artiklar för vilka leveransdatumet har bekräftats (inte tomma celler), använd "" som ett kriterium:
=SUMIFS(D2:D13;A2:A13,"Tält",C2:C13,"")Vi bytte precis "=" mot "" i den här formeln. Den hittar summan av tältobjekt med icke-tomma celler i kolumn C.
SUMIFS med OR Logic
Eftersom SUMIFS-funktionen fungerar på AND-logik, summerar den bara när alla villkor är uppfyllda. Men vad händer om du vill summera värde baserat på flera kriterier när något av kriterierna är uppfyllt. Tricket är att använda flera SUMIFS-funktioner.
Om du till exempel vill lägga till försäljningsbeloppet för antingen "Cykelställ" ELLER "Ryggsäck" när deras status är "Beställt", prova denna formel:
=SUMIFS(D2:D13;A2:A13,"Cykelställ",C2:C13,"Beställd") +SUMIFS(D2:D13,A2:A13,"Ryggsäck",C2:C13,"Beställd")Den första SUMIFS-funktionen kontrollerar två kriterier "Cykelställ" och "Beställd" och summerar beloppsvärdena i kolumn D. Sedan kontrollerar den andra SUMIFS två kriterier "Backpack" och "Ordered" och summerar beloppsvärdena i kolumn D. Och sedan , båda summorna adderas och visas på F3. Med enkla ord summerar denna formel när antingen "Cykelställ" eller "Ryggsäck" beställs.
Det är allt du behöver veta om SUMIF- och SUMIFS-funktionen i Google Sheets.