Denna handledning förklarar skillnaden mellan SUMIF- och SUMIFS-funktionerna i termer av deras syntax och användning och ger ett antal formelexempel för att summera värden med flera OCH / ELLER-kriterier i Excel 2019, 2016, 2013, 2010, 2007, 2003 och lägre.
Som alla vet tillhandahåller Microsoft Excel en matris funktioner för att utföra olika beräkningar med data. För några artiklar sedan utforskade vi COUNTIF och COUNTIFS, som är utformade för att räkna celler baserat på ett enda tillstånd respektive flera villkor. Förra veckan täckte vi Excel SUMIF som lägger till värden som uppfyller de angivna kriterierna. Nu är det dags att gå igenom pluralversionen av SUMIF – Excel SUMIFS som gör det möjligt att summera värden efter flera kriterier.
De som känner till SUMIF-funktionen kanske tror att det bara tar extra att konvertera det till SUMIFS ”S” och några ytterligare kriterier. Detta verkar vara ganska logiskt … men ”logiskt” så är det inte alltid fallet när det handlar om Microsoft:)
- SUMIF-funktion – syntax & användning
- SUMIFS-funktion – syntax & användning
- Hur man använder SUMIFS i Excel (OCH logik)
- Excel SUMIF med flera kriterier (ELLER logik)
- Excel SUMIFS med flera ELLER-villkor
- Använda Excel SUM i matrisformler
Excel SUMIF-funktion – syntax & användning
SUMIF-funktionen används för att villkorligt summera värden baserat på ett enda kriterium. Vi diskuterade SUMIF-syntaxen i detalj i föregående artikel, så låt mig ge dig en kort sammanfattning nu.
-
range– det antal celler som ska utvärderas enligt dina kriterier. -
criteria– villkoret som måste uppfyllas, krävs. -
sum_range– cellerna för att summera om villkoret är uppfyllt, valfritt.
Som du ser är syntax för Excel SUMIF-funktionen tillåter endast ett villkor. Och ändå säger vi att Excel SUMIF kan användas för att summera värden med flera kriterier. Hur kan det vara? Genom att lägga till resultaten från flera SUMIF-funktioner och använda SUMIF-formler med arraykriterier, vilket visas i exemplen som följer.
Excel SUMIFS-funktion – syntax & användning
Du använder SUMIFS i Excel för att hitta en villkorlig summa av värden baserat på flera kriterier. SUMIFS-funktionen introducerades i Excel 2007, så du kan använda den i alla moderna versioner av Excel 2019, 2016, 2013, 2010 och 2007.
Jämfört med SUMIF är SUMIFS-syntaxen lite mer komplex :
De första 3 argumenten är obligatoriska, ytterligare intervall och tillhörande kriterier är valfria.
-
sum_range– en eller flera celler att summera, krävs. Detta kan vara en enda cell, ett cellområde eller ett namngivet intervall. Endast celler med siffror summeras; tomma värden och textvärden ignoreras. -
criteria_range1– det första intervallet som ska utvärderas med tillhörande kriterier, krävs. -
criteria1– det första villkoret som måste uppfyllas, krävs. Du kan ange kriterierna i form av ett nummer, logiskt uttryck, cellreferens, text eller en annan Excel-funktion. Du kan till exempel använda kriterier som 10, ”> = 10”, A1, ”körsbär” eller TODAY (). -
criteria_range2, criteria2, …– dessa är ytterligare intervall och kriterier associerade med dem, valfritt. Du kan använda upp till 127 intervall / kriteriepar i SUMIFS-formler.
Och låt nu ” s ta en titt på Excel SUMIFS-formeln med två villkor. Antag att du har en tabell som visar fruktens sändningar från olika leverantörer. Du har fruktnamnen i kolumn A, leverantörernas namn i kolumn B och kvantitet i kolumn C. Vad du vill är att ta reda på en summa av belopp relaterade till en viss frukt och leverantör, t.ex. alla äpplen från Pete.
När du lär dig något nytt är det alltid en bra idé att börja med enkla saker. Så till att börja med, låt oss definiera alla argument för vår SUMIFS-formel:
- sum_range – C2: C9
- criteria_range1 – A2: A9
- criteria1 – ”äpplen”
- criteria_range2 – B2: B9
- criteria2 – ”Pete”
Sätt nu ihop ovanstående parametrar och du får följande SUMIFS-formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
För att förfina formeln ytterligare kan du ersätta textkriterierna ”äpplen” och ”Pete” med cellreferenser.I det här fallet behöver du inte ändra formeln för att beräkna mängden annan frukt från en annan leverantör:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Använda SUMIFS och SUMIF i Excel – saker att komma ihåg
Eftersom syftet med denna handledning är att täcka alla möjliga sätt att summera värden på flera villkor, kommer vi att diskutera formelexempel med båda funktionerna – Excel SUMIFS och SUMIF med flera kriterier. För att kunna använda dem korrekt måste du tydligt förstå vad dessa två funktioner har gemensamt och på vilket sätt de är olika.
Medan den gemensamma delen är tydlig – liknande destination och parametrar – skillnader är inte så uppenbara, men väldigt väsentliga.
1. Argumentordningens ordning
I Excel SUMIF- och SUMIFS-funktionerna är argumentens ordning annorlunda. I synnerhet är sum_range den första parameter i SUMIFS, men den är tredje i SUMIF.
Vid första anblicken kan det tyckas att Microsoft medvetet komplicerar inlärningskurvan för sina användare. eh, vid en närmare titt kommer du att se resonemanget bakom det. Poängen är att sum_range är valfritt i SUMIF. Om du utelämnar det, inga problem, kommer din SUMIF-formel att summera värden i intervallet (första parametern).
I SUMIFS är summargan mycket viktigt och obligatoriskt, och det är därför det kommer först. Förmodligen trodde Microsoft-killar att efter att ha lagt till det 10: e eller 100: e intervallet / kriterieparet, kanske någon glömmer att ange intervallet som ska summeras:)
2. Summaområdet och kriterieintervallet ska vara lika stora
I SUMIF-funktionen behöver argumentet sum_range inte nödvändigtvis ha samma storlek som områdesargumentet, så länge du har den övre vänstra cellen till höger. I Excel SUMIFS måste varje kriterieområde innehålla samma antal rader och kolumner som parametern sum_range.
Till exempel, =SUMIF(A2:A9,F1,C2:C18) returnerar rätt resultat eftersom Excel anser bara cellen längst upp till vänster i argumentet sum_range (C2 i detta exempel, vilket är korrekt), och innehåller sedan så många kolumner och rader som finns i intervallargumentet.
Okej, tillräckligt med strategi (dvs. teori) , låt oss komma in i taktiken (dvs. formelexempel:)
Hur man använder SUMIFS i Excel – formelexempel
För ett ögonblick sedan diskuterade vi en enkel SUMIFS-formel med två text På samma sätt kan du använda Excel SUMIFS med flera kriterier uttryckta med siffror, datum, logiska uttryck och andra Excel-funktioner.
Exempel 1. Excel SUMIFS med jämförelseoperatorer
Antag att i vår fruktleverantörstabell vill du summera alla leveranser av Mike med 200 eller fler. För att göra detta använder du jämförelseoperatören ”större än o r lika med ”(> =) i kriterier2 och få följande SUMIFS-formel:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Vi täckte alla möjliga jämförelseoperatörer i detalj när vi diskuterade Excel SUMIF-funktionen, samma operatorer kan användas i SUMIFS-kriterier. Till exempel, följande formel med tillbaka summan av alla värden i cellerna C2: C9 som är större än eller lika med 200 och mindre än eller lika med 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Exempel 2. Använda Excel SUMIFS med datum
Om du vill summera värden med flera kriterier baserat på det aktuella datumet, använd TODAY () -funktionen i din SUMIFS som visas nedan. Följande formel summerar värden i kolumn D om ett motsvarande datum i kolumn C faller inom de senaste sju dagarna, inklusive idag:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
På ett liknande sätt kan du använda Excel SUMIF-funktionen för att summera värden i ett visst datumintervall. Till exempel lägger följande SUMIFS-formel till värdena i cellerna C2: C9 om ett datum i kolumn B faller mellan 1-oktober-2014 och 31-oktober-2014.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Samma resultat kan uppnås genom att beräkna skillnaden mellan två SUMIF-funktioner, vilket visas i detta exempel – Hur man använder SUMIF för att summera värden i ett givet datumintervall. Excel SUMIFS är dock mycket lättare och mer begripligt, eller hur?
Exempel 3. Excel SUMIFS med tomma och icke-tomma celler
När du analyserar rapporter och andra data, kan ofta behöva summera värden som motsvarar antingen tomma eller icke-tomma celler.
Och nu, låt oss se hur du kan använda en SUMIFS-formel med ”tomt” och ”icke-tomt” kriterier på riktigt data.
Antag att du har ett orderdatum i kolumn B, leveransdatum i kolumn C och antal. i kolumn D. Hur hittar du det totala antalet produkter som ännu inte har levererats? Det vill säga att du vill veta summan av värden som motsvarar icke-tomma celler i kolumn B och tomma celler i kolumn C.
Lösningen är att använda SUMIFS-formeln med två kriterier:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Använda Excel SUMIF med flera ELLER-kriterier
Som noterades i början av denna handledning är SUMIFS-funktionen utformad med AND-logik. Men tänk om du behöver summera värden med flera ELLER-kriterier, dvs när minst ett av villkoren är uppfyllda?
Exempel 1. SUMIF + SUMIF
Den enklaste lösningen är att summera resultaten returneras av flera SUMIF-funktioner. Till exempel visar följande formel hur man hittar den totala produkten som levereras av Mike och John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Som du ser lägger den första SUMIF-funktionen till de kvantiteter som motsvarar ”Mike”, den andra SUMIF-funktionen returnerar beloppen relaterade till ”John” och sedan lägger du till dessa två siffror.
Exempel 2. SUM & SUMIF med ett arrayargument
Lösningen ovan är mycket enkel och kan få jobbet gjort snabbt när det bara finns ett par kriterier. Men en SUMIF + SUMIF-formel kan växa enormt om du vill summera värden med flera ELLER-villkor. I detta fall är ett bättre tillvägagångssätt att använda ett arraykriterierargument i SUMIF-funktionen. Låt oss undersöka detta tillvägagångssätt nu.
Du kan börja med att lista alla dina villkor åtskilda med kommatecken och sedan bifoga den resulterande kommaseparerade listan i {curly parentes}, som tekniskt kallas en array.
Arrayargumentet som består av tre värden tvingar din SUMIF-formel att returnera tre separata resultat, men eftersom vi skriver formeln i en enskild cell, skulle den bara returnera det första resultatet – dvs. det totala antalet produkter som levereras av John För att få denna matriskriterier att fungera måste du använda ytterligare ett litet trick – bifoga din SUMIF-formel i en SUM-funktion, så här:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Som du ser gör ett arraykriterium formeln mycket mer kompakt jämfört med SUMIF + SUMIF och låter dig lägga till så många värden som du gillar i matrisen.
Detta tillvägagångssätt fungerar både med siffror och med textvärden. Om du till exempel i stället för leverantörernas namn i kolumn C hade du leverantörs-ID som 1, 2, 3 etc ., då skulle din SUMIF-formel se ut som den här:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Till skillnad från textvärden behöver siffror inte bifogas dubbla citat i arrayargument.
Exempel 3. SUMPRODUCT & SUMIF
Om ditt föredragna sätt är att lista kriterierna i vissa celler snarare än ange dem direkt i formeln, du kan använda SUMIF i kombination med SUMPRODUCT-funktionen som multiplicerar komponenter i de angivna matriserna och returnerar summan av dessa produkter.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Där G2: G4 är cellerna som innehåller dina kriterier, namnen på leverantörerna i vårt fall, som illustreras i skärmdumpen nedan.
Men naturligtvis hindrar ingenting dig från listar värdena i ett arraykriterium för din SUMIF-funktion om du vill:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Resultatet som returneras av båda formlerna blir identisk med vad du ser på skärmdumpen:
Excel SUMIFS med flera ELLER-kriterier
Om du vill vill summera värden i Excel inte bara med flera ELLER-villkor, utan med flera uppsättningar villkor, måste du använda SUMIFS istället för SUMIF. Formlerna kommer att likna mycket det vi just diskuterat.
Som vanligt kan ett exempel hjälpa till att illustrera poängen bättre. I vår tabell över fruktleverantörer, låt oss lägga till leveransdatum (kolumn E) och hitta den totala kvantiteten som levererades av Mike, John och Pete i oktober.
Exempel 1. SUMIFS + SUMIFS
Formeln som produceras med detta tillvägagångssätt innehåller mycket repetition och utseende besvärligt, men det är lätt att förstå och, viktigast av allt, det fungerar:)
Exempel 2. SUM & SUMIFS med ett arrayargument
Jag har försökt förklara kärnan i detta tillvägagångssätt i SUMIF-exemplet, så nu kan vi helt enkelt kopiera den formeln, ändra ordningen på argumenten (som ni kommer ihåg är det annorlunda i SUMIF och SUMIFS) och lägga till ytterligare kriterier. resulterande formel är mer kompakt än SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Resultatet som returneras med denna formel är exakt samma som du ser i skärmdumpen ovan.
Exampl e 3. SUMPRODUCT & SUMIFS
Som du kommer ihåg skiljer sig SUMPRODUCT-metoden från de två föregående på det sättet att du anger var och en av dina kriterier i en separat cell snarare att specificera dem direkt i formeln. Om det finns flera kriterier, kommer SUMPRODUCT-funktionen inte att räcka och du måste också använda ISNUMBER och MATCH.
Så, förutsatt att leveransnamnen finns i celler H1: H3, startdatum är i cell H4 och slutdatum i cell H5, har vår SUMPRODUCT-formel följande form:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Många undrar varför använda dubbel streck (-) i SUMPRODUCT-formler. Poängen är att Excel SUMPRODUCT ignorerar alla utom numeriska värden, medan jämförelsesoperatorerna i vår formel returnerar booleska värden (TRUE / FALSE), som är icke-numeriska. För att konvertera dessa booleska värden till 1 ”s och 0” s använder du det dubbla minustecknet, som tekniskt kallas den dubbla unary-operatören. Den första unaryen tvingar SANT / FALSK till -1/0 respektive. Den andra unaryen förnekar värdena, dvs. vänder om tecknet och förvandlar dem till +1 och 0, som SUMPRODUCT-funktionen kan förstå.
Jag hoppas att ovanstående förklaring är meningsfull. Och även om det inte gör det, kom bara ihåg den här tumregeln – använd den dubbla unara operatören (-) när du använder jämförelseoperatorer i dina SUMPRODUCT-formler.
Använda Excel SUM i matrisformler
Som du kommer ihåg implementerade Microsoft SUMIFS-funktionen i Excel 2007. Om någon fortfarande använder Excel 2003, 2000 eller tidigare, måste du använda en SUM-matrisformel för att lägga till värden med flera OCH-kriterier. fungerar även i moderna versioner av Excel 2013 – 2007 och kan betraktas som en gammaldags motsvarighet till SUMIFS-funktionen.
I SUMIF-formlerna som diskuterats ovan har du redan använt arrayargument, men en arrayformel är något annorlunda.
Exempel 1. Summa med flera OCH-kriterier i Excel 2003 och tidigare
Låt oss återgå till det allra första exemplet där vi fick reda på en summa av belopp till en viss frukt och leverantör:
Som du redan vet görs denna uppgift enkelt med en vanlig SUMIFS-formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Och nu ska vi se hur samma uppgift kan utföras i tidiga ”SUMIFS-fria” versioner av Excel . Först och främst skriver du ner alla villkor som ska uppfyllas i form av intervall = ”villkor”. I det här exemplet har vi två intervall / villkorspar:
Villkor 1: A2: A9 = ”äpplen”
Villkor 2: B2: B9 = ”Pete”
Sedan skriver du en SUM-formel som ”multiplicerar” alla dina intervall / villkorspar, var och en i parentes. Den sista multiplikatorn är summan, C2: C9 i vårt fall:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Som illustreras i skärmdumpen nedan är formeln fungerar perfekt i den senaste versionen av Excel 2013.
Exempel 2. SUM-matrisformler i moderna Excel-versioner
Even i moderna versioner av Excel bör kraften i SUM-funktionen inte underskattas. SUM-matrisformeln är inte bara sinnets gymnastik utan har ett praktiskt värde, vilket visas i följande exempel.
Antag , du har två kolumner, B och C, och du måste räkna hur många gånger kolumn C är större än kolumn B, när ett värde i kolumn C är större eller lika med 10. En omedelbar lösning som kommer att tänka på är att använda SUM matrisformel:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Se inte något praktiskt tillämpning på ovanstående formel? Tänk på det på ett annat sätt:)
Antag att du har orderlistan som visas på skärmdumpen nedan och du vill veta hur många produkter som inte har levererats i sin helhet vid ett visst datum. Översatt till Excel: s språk har vi följande villkor:
Villkor 1: Ett värde i kolumn B (Beställda artiklar) är större än 0
Villkor 2: Ett värde i kolumn C (Levereras) i mindre än i kolumn B
Villkor 3: Ett datum i kolumn D (Förfallodatum) är mindre än 2014-01-11.
Att sätta de tre intervallet / villkor parar ihop, du får följande formel:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Tja, formelexemplen som diskuteras i den här självstudien har bara repat ytan på vad Excel SUMIFS och SUMIF-funktionerna verkligen kan göra. Men förhoppningsvis har de hjälpt till att peka dig i rätt riktning och nu kan du summera värden i dina Excel-arbetsböcker oavsett hur många invecklade förhållanden du måste tänka på.
- Hur man använder SUMIF i Excel – formelexempel för att villkorligt summera celler
- Hur man använder Excel SUMIFS och SUMIF med flera kriterier – formelexempel
- Hur man använder VLOOKUP & SUM- eller SUMIF-funktioner i Excel
- SUMIF i Google Sheets med formelexempel
- COUNTIF i Excel – räkna om inte tomt, större än, duplicera eller unikt
- Hur man räknar och summerar celler efter färg i Excel