Denne vejledning forklarer forskellen mellem SUMIF- og SUMIFS-funktionerne med hensyn til deres syntaks og brug og giver et antal formeleksempler for at opsummere værdier med flere AND / OR-kriterier i Excel 2019, 2016, 2013, 2010, 2007, 2003 og lavere.
Som alle ved, tilbyder Microsoft Excel en matrix af funktioner til at udføre forskellige beregninger med data. For et par artikler siden undersøgte vi COUNTIF og COUNTIFS, der er designet til at tælle celler baseret på henholdsvis en enkelt tilstand og flere betingelser. Sidste uge dækkede vi Excel SUMIF, der tilføjer værdier, der opfylder de angivne kriterier. Nu er det tid til at gå gennem flertalsversionen af SUMIF – Excel SUMIFS, der giver mulighed for at opsummere værdier efter flere kriterier.
De, der er fortrolige med SUMIF-funktionen, tror måske, at konvertering af det til SUMIFS kun tager en ekstra “S” og et par yderligere kriterier. Dette virker ganske logisk … men “logisk” er det ikke altid tilfældet, når du beskæftiger dig med Microsoft:)
- SUMIF-funktion – syntaks & brug
- SUMIFS-funktion – syntaks & -brug
- Sådan bruges SUMIFS i Excel (OG logik)
- Excel SUMIF med flere kriterier (ELLER logik)
- Excel SUMIFS med flere ELLER betingelser
- Brug af Excel SUM i matrixformler
Excel SUMIF-funktion – syntaks & -forbrug
SUMIF-funktionen bruges til betinget at summere værdier baseret på et enkelt kriterium. Vi diskuterede SUMIF-syntaksen detaljeret i den forrige artikel, så lad mig give dig et hurtigt resumé nu.
-
range– det område af celler, der skal evalueres efter dine kriterier, krævet. -
criteria– den betingelse, der skal være opfyldt, krævet. -
sum_range– cellerne, der skal opsummeres, hvis betingelsen er opfyldt, valgfri.
Som du ser, er syntaksen for Excel SUMIF-funktionen tillader kun en betingelse. Og stadig siger vi, at Excel SUMIF kan bruges til at opsummere værdier med flere kriterier. Hvordan kan det være? Ved at tilføje resultaterne af flere SUMIF-funktioner og ved at bruge SUMIF-formler med matrixkriterier, som vist i de følgende eksempler.
Excel SUMIFS-funktion – syntaks & -brug
Du bruger SUMIFS i Excel til at finde en betinget sum af værdier baseret på flere kriterier. SUMIFS-funktionen blev introduceret i Excel 2007, så du kan bruge den i alle moderne versioner af Excel 2019, 2016, 2013, 2010 og 2007.
Sammenlignet med SUMIF er SUMIFS-syntaksen lidt mere kompleks :
De første 3 argumenter er obligatoriske, yderligere intervaller og deres tilknyttede kriterier er valgfri.
-
sum_range– en eller flere celler, der skal summeres, påkrævet. Dette kan være en enkelt celle, et celleområde eller et navngivet område. Kun celler med tal summeres; tomme og tekstværdier ignoreres. -
criteria_range1– det første interval, der skal evalueres ud fra de tilknyttede kriterier, krævet. -
criteria1– den første betingelse, der skal være opfyldt, krævet. Du kan angive kriterierne i form af et tal, et logisk udtryk, en cellereference, en tekst eller en anden Excel-funktion. For eksempel kan du bruge kriterier som 10, “> = 10”, A1, “kirsebær” eller I DAG (). -
criteria_range2, criteria2, …– dette er yderligere intervaller og kriterier, der er knyttet til dem, valgfri. Du kan bruge op til 127 række / kriteriepar i SUMIFS-formler.
Og lad os nu ” s se på Excel SUMIFS-formlen med to betingelser. Antag, du har en tabel, der viser frugtsendingerne fra forskellige leverandører. Du har frugtnavne i kolonne A, leverandørernes navne i kolonne B og mængde i kolonne C. Hvad du ønsker er at finde ud af et beløb, der vedrører en given frugt og leverandør, f.eks alle æbler leveret af Pete.
Når du lærer noget nyt, er det altid en god idé at starte med enkle ting. Så til at begynde med skal vi definere alle argumenterne for vores SUMIFS-formel:
- sum_range – C2: C9
- criteria_range1 – A2: A9
- criteria1 – “æbler”
- criteria_range2 – B2: B9
- criteria2 – “Pete”
Saml nu ovenstående parametre, og du får følgende SUMIFS-formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
For at forfine formlen yderligere kan du erstatte tekstkriterierne “æbler” og “Pete” med cellereferencer.I dette tilfælde behøver du ikke ændre formlen for at beregne mængden af anden frugt fra en anden leverandør:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Brug af SUMIFS og SUMIF i Excel – ting at huske
Da formålet med denne tutorial er at dække alle mulige måder at opsummere værdier på flere betingelser, vil vi diskutere formeleksempler med begge funktioner – Excel SUMIFS og SUMIF med flere kriterier. For at bruge dem korrekt skal du forstå, hvad disse to funktioner har til fælles, og på hvilken måde de er forskellige.
Mens den fælles del er klar – lignende destination og parametre – forskellene er ikke så indlysende, selvom de er meget vigtige.
1. Argumenternes rækkefølge
I Excel SUMIF- og SUMIFS-funktionerne er rækkefølgen af argumenter anderledes. Specielt er sum_range den 1. parameter i SUMIFS, men det er 3. i SUMIF.
Ved første øjekast ser det ud til, at Microsoft bevidst komplicerer læringskurven for sine brugere. er, ved et nærmere kig, vil du se ræsonnementet bag det. Pointen er, at sum_range er valgfri i SUMIF. Hvis du udelader det, ikke noget problem, summerer din SUMIF-formel værdier i området (første parameter).
I SUMIFS er sum_range meget vigtig og obligatorisk, og det er derfor, det kommer først. Sandsynligvis troede Microsoft fyre, at nogen efter at have tilføjet det 10. eller 100. interval / kriteriepar, måske glemmer at angive området, der skal summeres:)
2. Sommeinterval og kriterieområde skal være lige store
I SUMIF-funktionen behøver argumentet sum_range ikke nødvendigvis at have samme størrelse som rækkevidden, så længe du har cellen øverst til venstre til højre. I Excel SUMIFS skal hvert kriterieområde indeholde det samme antal rækker og kolonner som parameteren sum_range.
For eksempel vil =SUMIF(A2:A9,F1,C2:C18) returnere det korrekte resultat, fordi Excel betragter kun cellen øverst til venstre i sum_range-argumentet (C2 i dette eksempel, hvilket er korrekt) og inkluderer så mange kolonner og rækker som indeholdt i områdeargumentet.
Okay, nok strategi (dvs. teori) , lad os komme ind i taktikken (dvs. formeleksempler:)
Sådan bruges SUMIFS i Excel – formeleksempler
For et øjeblik siden diskuterede vi en simpel SUMIFS-formel med to tekst På samme måde kan du bruge Excel SUMIFS med flere kriterier udtrykt ved tal, datoer, logiske udtryk og andre Excel-funktioner.
Eksempel 1. Excel SUMIFS med sammenligningsoperatorer
Antag, i vores frugtleverandørtabel, at du vil sammenfatte alle leveringer af Mike med antal 200 eller mere. For at gøre dette bruger du sammenligningsoperatoren “større end o r svarer til “(> =) i kriterier2 og få følgende SUMIFS-formel:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Vi dækkede alle mulige sammenligningsoperatører i detaljer, når vi diskuterede Excel SUMIF-funktionen, de samme operatorer kan bruges i SUMIFS-kriterier. For eksempel returnerer følgende formel med summen af alle værdier i celler C2: C9, der er større end eller lig med 200 og mindre end eller lig med 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Eksempel 2. Brug af Excel SUMIFS med datoer
Hvis du vil sammenfatte værdier med flere kriterier baseret på den aktuelle dato, skal du bruge funktionen DAG () i din SUMIFS kriterier, som vist nedenfor. Følgende formel summerer værdier i kolonne D, hvis en tilsvarende dato i kolonne C falder inden for de sidste syv dage, inklusive i dag:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
På lignende måde kan du bruge Excel SUMIF-funktionen til at summere værdier i et givet datointerval. For eksempel tilføjer følgende SUMIFS-formel værdierne i cellerne C2: C9, hvis en dato i kolonne B falder mellem 1. oktober 2014 og 31. oktober 2014 inklusive.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Det samme resultat kan opnås ved at beregne forskellen på to SUMIF-funktioner, som vist i dette eksempel – Sådan bruges SUMIF til at summere værdier i et givet datointerval. Excel SUMIFS er dog meget nemmere og mere forståelig, er det ikke?
Eksempel 3. Excel SUMIFS med tomme og ikke-tomme celler
Når du analyserer rapporter og andre data, skal du det kan ofte være nødvendigt at summere værdier, der svarer til enten tomme eller ikke-tomme celler.
Og lad os nu se, hvordan du kan bruge en SUMIFS-formel med “tomme” og “ikke-tomme” kriterier på ægte data.
Antag at du har en ordredato i kolonne B, leveringsdato i kolonne C og antal. i kolonne D. Hvordan finder du det samlede antal produkter, der endnu ikke er leveret? Det vil sige, at du vil vide summen af værdier, der svarer til ikke-tomme celler i kolonne B og tomme celler i kolonne C.
Løsningen er at bruge SUMIFS-formlen med 2 kriterier:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Brug af Excel SUMIF med flere ELLER kriterier
Som bemærket i begyndelsen af denne tutorial er SUMIFS-funktionen designet med AND-logik. Men hvad hvis du har brug for at summere værdier med flere ELLER-kriterier, dvs. når mindst en af betingelserne er opfyldt?
Eksempel 1. SUMIF + SUMIF
Den enkleste løsning er at opsummere resultaterne returneres af flere SUMIF-funktioner. For eksempel viser følgende formel, hvordan man finder det samlede antal produkter leveret af Mike og John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Som du ser tilføjer den første SUMIF-funktion de mængder, der svarer til “Mike”, den anden SUMIF-funktion returnerer de beløb, der vedrører “John”, og derefter tilføjer du disse 2 tal.
Eksempel 2. SUM & SUMIF med et array-argument
Ovenstående løsning er meget enkel og kan muligvis få jobbet gjort hurtigt, når der kun er et par kriterier. Men en SUMIF + SUMIF-formel kan vokse enormt op, hvis du vil sammenfatte værdier med flere ELLER betingelser. I dette tilfælde bruger en bedre tilgang et array-kriteriumargument i SUMIF-funktionen. Lad os undersøge denne tilgang nu.
Du kan starte med at liste alle dine betingelser adskilt af kommaer og derefter vedlægge den resulterende komma-adskilte liste i {krøllede parenteser}, som teknisk kaldes en matrix.
Arrayargumentet bestående af 3 værdier tvinger din SUMIF-formel til at returnere tre separate resultater, men da vi skriver formlen i en enkelt celle, ville den kun returnere det første resultat – dvs. det samlede antal produkter leveret af John For at få denne array-kriterietilgang til at fungere, skal du bruge endnu et lille trick – vedlæg din SUMIF-formel i en SUM-funktion som denne:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Som du ser, gør et array-kriterium formlen meget mere kompakt sammenlignet med SUMIF + SUMIF, og lader dig tilføje så mange værdier som du kan lide i matrixen.
Denne fremgangsmåde fungerer med tal såvel som med tekstværdier. Hvis du f.eks. i stedet for leverandørernes “navne i kolonne C havde leverandør-ider som 1, 2, 3 osv. ., så ser din SUMIF-formel ud som denne:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
I modsætning til tekstværdier behøver tal ikke at være omsluttet med dobbelt anførselstegn i arrayargumenter.
Eksempel 3. SUMPRODUCT & SUMIF
Hvis din foretrukne måde er at angive kriterierne i nogle celler i stedet for at angiv dem direkte i formlen, du kan bruge SUMIF sammen med SUMPRODUCT-funktionen, der multiplicerer komponenter i de givne arrays og returnerer summen af disse produkter.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Hvor G2: G4 er cellerne, der indeholder dine kriterier, hedder leverandørernes “i vores tilfælde, som illustreret i skærmbilledet nedenfor.
Men selvfølgelig forhindrer intet dig i at viser værdierne i en matrixkriterier for din SUMIF-funktion, hvis du vil:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Resultatet, der returneres af begge formler, bliver identisk med hvad du ser på skærmbilledet:
Excel SUMIFS med flere ELLER-kriterier
Hvis du vil opsummere værdier i Excel ikke blot med flere ELLER-betingelser, men med flere sæt betingelser, skal du bruge SUMIFS i stedet for SUMIF. Formlerne vil være meget lig det, vi netop har diskuteret.
Som sædvanligt kan et eksempel hjælpe med at illustrere pointen bedre. I vores tabel med frugtleverandører, lad os tilføje leveringsdato (kolonne E) og find den samlede mængde leveret af Mike, John og Pete i oktober.
Eksempel 1. SUMIFS + SUMIFS
Formlen, der produceres ved denne fremgangsmåde, indeholder en masse gentagelser og udseende besværligt, men det er let at forstå, og vigtigst af alt fungerer det 🙂
Eksempel 2. SUM & SUMIFS med et arrayargument
Jeg har forsøgt at forklare essensen af denne tilgang i SUMIF-eksemplet, så nu kan vi simpelthen kopiere den formel, ændre rækkefølgen af argumenter (som du husker, er den anderledes i SUMIF og SUMIFS) og tilføje yderligere kriterier. resulterende formel er mere kompakt end SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Resultatet, der returneres med denne formel, er nøjagtigt det samme som du ser i skærmbilledet ovenfor.
Eksempel e 3. SUMPRODUCT & SUMIFS
Som du husker, adskiller SUMPRODUCT-metoden sig fra de to foregående på den måde, at du indtaster hvert af dine kriterier i en separat celle snarere at angive dem direkte i formlen. I tilfælde af flere kriteriesæt, vil SUMPRODUCT-funktionen ikke være tilstrækkelig, og du bliver også nødt til at anvende ISNUMBER og MATCH.
Så forudsat at forsyningsnavne er i celler H1: H3, startdato er i celle H4 og slutdato i celle H5, har vores SUMPRODUCT-formel følgende form:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Mange mennesker undrer sig over, hvorfor de bruger dobbelt bindestreg (-) i SUMPRODUCT-formler. Pointen er, at Excel SUMPRODUCT ignorerer alle undtagen numeriske værdier, mens sammenligningsoperatorerne i vores formel returnerer boolske værdier (TRUE / FALSE), som ikke er numeriske. For at konvertere disse Boolske værdier til 1 “s og 0” s, bruger du det dobbelte minustegn, som teknisk kaldes den dobbelte unære operator. Den første unary tvinger TRUE / FALSE til henholdsvis -1/0. Den anden unary negerer værdierne, dvs. vender tegnet og omdanner dem til +1 og 0, som SUMPRODUCT-funktionen kan forstå.
Jeg håber, at ovenstående forklaring giver mening. Og selvom det ikke gør det, skal du bare huske denne tommelfingerregel – brug den dobbelte unary operator (-), når du bruger sammenligningsoperatorer i dine SUMPRODUCT-formler.
Brug af Excel SUM i array-formler
Som du husker, implementerede Microsoft SUMIFS-funktionen i Excel 2007. Hvis nogen stadig bruger Excel 2003, 2000 eller tidligere, skal du bruge en SUM-matrixformel til at tilføje værdier med flere OG-kriterier. Naturligvis er denne tilgang fungerer også i moderne versioner af Excel 2013 – 2007 og kan betragtes som en gammeldags modstykke til SUMIFS-funktionen.
I SUMIF-formlerne beskrevet ovenfor har du allerede brugt arrayargumenter, men en arrayformel er noget andet.
Eksempel 1. Summen med flere OG-kriterier i Excel 2003 og tidligere
Lad os vende tilbage til det allerførste eksempel, hvor vi fandt ud af et beløb til en given frugt og leverandør:
Som du allerede ved, udføres denne opgave let ved hjælp af en almindelig SUMIFS-formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Og lad os nu se, hvordan den samme opgave kan udføres i tidlige “SUMIFS-fri” versioner af Excel . Først skal du nedskrive alle de betingelser, der skal være opfyldt i form af interval = “betingelse”. I dette eksempel har vi to række / tilstandspar:
Betingelse 1: A2: A9 = “æbler”
Betingelse 2: B2: B9 = “Pete”
Derefter skriver du en SUM-formel, der “multiplicerer” alle dine række / tilstandspar, hver lukket i parentes. Den sidste multiplikator er sumområdet, C2: C9 i vores tilfælde:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Som illustreret i skærmbilledet nedenfor er formlen fungerer perfekt i den nyeste Excel 2013-version.
Eksempel 2. SUM-matrixformler i moderne Excel-versioner
Selv i moderne versioner af Excel bør kraften i SUM-funktionen ikke undervurderes. SUM-matrixformlen er ikke kun gymnastik i sindet, men har en praktisk værdi som vist i det følgende eksempel.
Antag , har du to kolonner, B og C, og du skal tælle, hvor mange gange kolonne C er større end kolonne B, når en værdi i kolonne C er større eller lig med 10. En øjeblikkelig løsning, der kommer til at tænke på, er at bruge SUM matrixformel:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Se ikke noget praktisk anvendelse til ovenstående formel? Tænk over det på en anden måde:)
Antag, du har ordrelisten som vist på skærmbilledet nedenfor, og du vil vide, hvor mange produkter der ikke er leveret fuldt ud inden for en given dato. Oversat til Excel sprog har vi følgende betingelser:
Betingelse 1: En værdi i kolonne B (bestilte varer) er større end 0
Betingelse 2: En værdi i kolonne C (Leveret) i mindre end i kolonne B
Betingelse 3: En dato i kolonne D (Forfaldsdato) er mindre end 11/1/2014.
At sætte de tre intervaller / betingelsespar sammen får du følgende formel:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Nå, formeleksemplerne, der er diskuteret i denne vejledning, har kun ridset overfladen af, hvad Excel SUMIFS og SUMIF-funktioner virkelig kan gøre. Men forhåbentlig har de hjulpet med at pege dig i den rigtige retning, og nu kan du opsummere værdier i dine Excel-projektmapper hvor mange indviklede forhold du skal overveje.
- Sådan bruges SUMIF i Excel – formeleksempler til betinget summering af celler
- Sådan bruges Excel SUMIFS og SUMIF med flere kriterier – formeleksempler
- Sådan bruges VLOOKUP & SUM- eller SUMIF-funktioner i Excel
- SUMIF i Google Sheets med formeleksempler
- COUNTIF i Excel – tæl hvis ikke tom, større end, duplikat eller unik
- Sådan tælles og summeres celler efter farve i Excel