Denne veiledningen forklarer forskjellen mellom SUMIF- og SUMIFS-funksjonene når det gjelder syntaks og bruk, og gir en rekke formeleksempler for å summere verdier med flere OG / ELLER-kriterier i Excel 2019, 2016, 2013, 2010, 2007, 2003 og lavere.
Som alle vet, tilbyr Microsoft Excel en matrise av funksjoner for å utføre ulike beregninger med data. For noen få artikler siden utforsket vi COUNTIF og COUNTIFS, som er designet for å telle celler basert på henholdsvis en enkelt tilstand og flere forhold. I forrige uke dekket vi Excel SUMIF som legger til verdier som oppfyller de angitte kriteriene. Nå er det på tide å gå gjennom flertallsversjonen av SUMIF – Excel SUMIFS som gjør det mulig å summere verdier etter flere kriterier.
De som er kjent med SUMIF-funksjonen, tror kanskje at det å konvertere det til SUMIFS tar bare en ekstra «S» og noen ekstra kriterier. Dette virker ganske logisk … men «logisk» er det ikke alltid tilfelle når du arbeider med Microsoft:)
- SUMIF-funksjon – syntaks & bruk
- SUMIFS-funksjon – syntaks & bruk
- Hvordan bruke SUMIFS i Excel (OG logikk)
- Excel SUMIF med flere kriterier (ELLER logikk)
- Excel SUMIFS med flere ELLER betingelser
- Bruk av Excel SUM i matriseformler
Excel SUMIF-funksjon – syntaks & bruk
SUMIF-funksjonen brukes til å betinget summere verdier basert på et enkelt kriterium. Vi diskuterte SUMIF-syntaksen i detalj i forrige artikkel, så la meg gi deg et raskt sammendrag nå.
-
range– celleområdet som skal evalueres etter dine kriterier, nødvendig. -
criteria– vilkåret som må oppfylles, kreves. -
sum_range– cellene som skal summeres hvis betingelsen er oppfylt, valgfritt.
Som du ser, er syntaksen til Excel SUMIF-funksjonen tillater bare en betingelse. Og likevel sier vi at Excel SUMIF kan brukes til å summere verdier med flere kriterier. Hvordan kan det være? Ved å legge til resultatene fra flere SUMIF-funksjoner og ved å bruke SUMIF-formler med matrixkriterier, som vist i eksemplene som følger.
Excel SUMIFS-funksjon – syntaks & bruk
Du bruker SUMIFS i Excel for å finne en betinget sum av verdier basert på flere kriterier. SUMIFS-funksjonen ble introdusert i Excel 2007, slik at du kan bruke den i alle moderne versjoner av Excel 2019, 2016, 2013, 2010 og 2007.
Sammenlignet med SUMIF er SUMIFS-syntaksen litt mer kompleks :
De første 3 argumentene er obligatoriske, tilleggsområder og deres tilknyttede kriterier er valgfrie.
-
sum_range– en eller flere celler å summere, påkrevd. Dette kan være en enkelt celle, et celleområde eller et navngitt område. Bare celler med tall er summert; tomme og tekstverdier ignoreres. -
criteria_range1– det første området som skal evalueres ut fra de tilknyttede kriteriene, nødvendig. -
criteria1– den første betingelsen som må oppfylles, kreves. Du kan oppgi kriteriene i form av et tall, logisk uttrykk, cellereferanse, tekst eller en annen Excel-funksjon. For eksempel kan du bruke kriterier som 10, «> = 10», A1, «kirsebær» eller I DAG (). -
criteria_range2, criteria2, …– dette er tilleggsområder og kriterier knyttet til dem, valgfritt. Du kan bruke opptil 127 rekkevidde / kriteriepar i SUMIFS-formler.
Og la oss la oss nå » s ta en titt på Excel SUMIFS-formelen med to betingelser. Anta at du har en tabell som viser sendingene av frukt fra forskjellige leverandører. Du har fruktnavnene i kolonne A, leverandørenes navn i kolonne B og mengden i kolonne C. Det du ønsker er å finne ut en sum av beløp knyttet til en gitt frukt og leverandør, f.eks alle epler levert av Pete.
Når du lærer noe nytt, er det alltid lurt å begynne med enkle ting. Så til å begynne med, la oss definere alle argumentene for SUMIFS-formelen:
- sum_range – C2: C9
- criteria_range1 – A2: A9
- kriterier1 – «epler»
- kriterier_område2 – B2: B9
- kriterier2 – «Pete»
Sett nå sammen parametrene ovenfor, og du får følgende SUMIFS-formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
For å finpusse formelen videre, kan du erstatte tekstkriteriene «epler» og «Pete» med cellereferanser.I dette tilfellet trenger du ikke endre formelen for å beregne mengden annen frukt fra en annen leverandør:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Bruke SUMIFS og SUMIF i Excel – ting å huske
Siden målet med denne opplæringen er å dekke alle mulige måter å oppsummere verdier med flere forhold, vil vi diskutere formeleksempler med begge funksjonene – Excel SUMIFS og SUMIF med flere kriterier. For å bruke dem riktig, må du forstå hva disse to funksjonene har til felles, og på hvilken måte de er forskjellige.
Mens den felles delen er klar – lik destinasjon og parametere – forskjellene er ikke så åpenbare, men veldig viktige.
1. Argumentrekkefølgen
I Excel SUMIF- og SUMIFS-funksjonene er rekkefølgen på argumentene forskjellig. Spesielt er sum_range den første parameter i SUMIFS, men den er tredje i SUMIF.
Ved første øyekast kan det virke som om Microsoft bevisst kompliserer læringskurven for brukerne. er, ved en nærmere titt, vil du se resonnementet bak det. Poenget er at sum_range er valgfritt i SUMIF. Hvis du utelater det, ikke noe problem, vil SUMIF-formelen summere verdier i området (første parameter).
I SUMIFS er sum_range veldig viktig og obligatorisk, og det er derfor den kommer først. Sannsynligvis trodde Microsoft-gutter at etter å ha lagt til det 10. eller 100. området / kriterieparet, noen kanskje glemmer å spesifisere området som skal summeres:)
2. Sumområdet og kriterieområdet skal være like store
I SUMIF-funksjonen trenger ikke argumentet sum_range nødvendigvis å ha samme størrelse som rekkevidden, så lenge du har cellen øverst til venstre til høyre. I Excel SUMIFS må hvert kriterieområde inneholde det samme antallet rader og kolonner som parameteren sum_range.
For eksempel vil =SUMIF(A2:A9,F1,C2:C18) returnere riktig resultat fordi Excel vurderer bare den øverste cellen øverst til venstre i sum_range-argumentet (C2 i dette eksemplet, som er riktig), og inkluderer så mange kolonner og rader som finnes i områdeargumentet.
OK, nok strategi (dvs. teori) , la oss komme inn i taktikken (dvs. formeleksempler:)
Hvordan bruke SUMIFS i Excel – formeleksempler
For et øyeblikk siden diskuterte vi en enkel SUMIFS-formel med to tekst På samme måte kan du bruke Excel SUMIFS med flere kriterier uttrykt med tall, datoer, logiske uttrykk og andre Excel-funksjoner.
Eksempel 1. Excel SUMIFS med sammenligningsoperatorer
Anta at du vil oppsummere alle leveranser av Mike med antall 200 eller mer. For å gjøre dette bruker du sammenligningsoperatøren «større enn o r lik «(> =) i kriterier2 og få følgende SUMIFS-formel:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Vi dekket alle mulige sammenligningsoperatører i detalj når vi diskuterte Excel SUMIF-funksjonen, de samme operatorene kan brukes i SUMIFS-kriterier. For eksempel returnerer følgende formel med summen av alle verdiene i celler C2: C9 som er større enn eller lik 200 og mindre enn eller lik 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Eksempel 2. Bruke Excel SUMIFS med datoer
Hvis du vil summere verdier med flere kriterier basert på gjeldende dato, bruk TODAY () -funksjonen i SUMIFS som vist nedenfor. Følgende formel summerer verdier i kolonne D hvis en tilsvarende dato i kolonne C faller innen de siste 7 dagene, inkludert i dag:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
På lignende måte kan du bruke Excel SUMIF-funksjonen til å summere verdier i et gitt datoperiode. Følgende SUMIFS-formel legger for eksempel til verdiene i celler C2: C9 hvis en dato i kolonne B faller mellom 1. oktober 2014 og 31. oktober 2014, inkludert.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Det samme resultatet kan oppnås ved å beregne forskjellen på to SUMIF-funksjoner, som vist i dette eksemplet – Hvordan bruke SUMIF til å summere verdier i et gitt datoperiode. Excel SUMIFS er imidlertid mye enklere og mer forståelig, er det ikke?
Eksempel 3. Excel SUMIFS med blanke og ikke-tomme celler
Når du analyserer rapporter og andre data, kan ofte trenge å oppsummere verdier som tilsvarer enten tomme eller ikke-tomme celler.
Og la oss nå se hvordan du kan bruke en SUMIFS-formel med «tomme» og «ikke-tomme» kriterier på reell data.
Anta at du har en bestillingsdato i kolonne B, leveringsdato i kolonne C og antall. i kolonne D. Hvordan finner du totalt antall produkter som ikke er levert ennå? Det vil si at du vil vite summen av verdier som tilsvarer ikke-tomme celler i kolonne B og tomme celler i kolonne C.
Løsningen er å bruke SUMIFS-formelen med to kriterier:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Bruke Excel SUMIF med flere ELLER kriterier
Som nevnt i begynnelsen av denne opplæringen, er SUMIFS-funksjonen designet med OG-logikk. Men hva om du trenger å summere verdier med flere ELLER kriterier, dvs. når minst en av betingelsene er oppfylt?
Eksempel 1. SUMIF + SUMIF
Den enkleste løsningen er å summere resultatene returnert av flere SUMIF-funksjoner. For eksempel demonstrerer følgende formel hvordan du finner totalt antall produkter levert av Mike og John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Som du ser, legger den første SUMIF-funksjonen til mengdene som tilsvarer «Mike», den andre SUMIF-funksjonen returnerer beløpene knyttet til «John», og deretter legger du til disse 2 tallene.
Eksempel 2. SUM & SUMIF med et arrayargument
Løsningen ovenfor er veldig enkel og kan gjøre jobben raskt når det bare er et par kriterier. Men en SUMIF + SUMIF-formel kan vokse opp enormt hvis du vil summere verdier med flere ELLER-forhold. I dette tilfellet er en bedre tilnærming å bruke et arraykriteriumargument i SUMIF-funksjonen. La oss undersøke denne tilnærmingen nå.
Du kan starte med å liste opp alle forholdene dine atskilt med kommaer, og deretter legge den resulterende kommaadskilte listen i {krøllete parenteser}, som teknisk kalles en matrise.
Arrayargumentet som består av 3 verdier tvinger SUMIF-formelen din til å returnere tre separate resultater, men siden vi skriver formelen i en enkelt celle, vil den bare returnere det første resultatet – dvs. totalt antall produkter levert av John For å få denne array-kriterietilnærmingen til å fungere, må du bruke et lite triks til – legg inn SUMIF-formelen i en SUM-funksjon, slik:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Som du ser, gjør en matrisekriterie formelen mye mer kompakt sammenlignet med SUMIF + SUMIF, og lar deg legge til så mange verdier som du liker i matrisen.
Denne tilnærmingen fungerer både med tall og med tekstverdier. Hvis du for eksempel hadde leverandør-ID som 1, 2, 3 osv. i stedet for leverandørenes navn i kolonne C ., da vil SUMIF-formelen se ut som denne:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
I motsetning til tekstverdier, trenger tall ikke å være omsluttet av dobbelt anførselstegn i arrayargumenter.
Eksempel 3. SUMPRODUCT & SUMIF
Hvis din foretrukne måte er å liste opp kriteriene i noen celler i stedet for at angi dem direkte i formelen, kan du bruke SUMIF i forbindelse med SUMPRODUCT-funksjonen som multipliserer komponenter i de gitte matriser, og returnerer summen av disse produktene.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Hvor G2: G4 er cellene som inneholder kriteriene dine, heter leverandørene i vårt tilfelle, som illustrert i skjermbildet nedenfor.
Men selvfølgelig hindrer ingenting deg i å oppføring av verdiene i en matrisekriterium for SUMIF-funksjonen hvis du vil:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Resultatet som returneres av begge formlene vil være identisk med det du ser på skjermbildet:
Excel SUMIFS med flere ELLER-kriterier
Hvis du ønsker å summere verdier i Excel ikke bare med flere ELLER-forhold, men med flere sett med betingelser, må du bruke SUMIFS i stedet for SUMIF. Formlene vil være veldig like det vi nettopp har diskutert.
Som vanlig kan et eksempel være med på å illustrere poenget bedre. I tabellen vår over fruktleverandører, la oss legge til leveringsdato (kolonne E) og finn den totale mengden levert av Mike, John og Pete i oktober.
Eksempel 1. SUMIFS + SUMIFS
Formelen produsert av denne tilnærmingen inkluderer mye repetisjon og utseende tungvint, men det er lett å forstå, og viktigst av alt, det fungerer:)
Eksempel 2. SUM & SUMIFS med et arrayargument
Jeg har prøvd å forklare essensen av denne tilnærmingen i SUMIF-eksemplet, så nå kan vi bare kopiere den formelen, endre rekkefølgen på argumenter (som du husker er den forskjellig i SUMIF og SUMIFS) og legge til flere kriterier. resulterende formel er mer kompakt enn SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Resultatet som returneres av denne formelen er nøyaktig det samme som du ser i skjermbildet ovenfor.
Eksempel e 3. SUMPRODUCT & SUMIFS
Som du husker, skiller SUMPRODUCT-tilnærmingen seg fra de to foregående på den måten at du skriver inn hvert av kriteriene dine i en egen celle heller som spesifiserer dem direkte i formelen. Ved flere kriteriesett, vil ikke SUMPRODUCT-funksjonen være tilstrekkelig, og du må også bruke ISNUMBER og MATCH.
Så, forutsatt at forsyningsnavnene er i cellene H1: H3, startdatoen er i celle H4 og sluttdatoen i cellen H5, har SUMPRODUCT-formelen følgende form:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Mange lurer på hvorfor du bruker dobbel bindestrek (-) i SUMPRODUCT-formler. Poenget er at Excel SUMPRODUCT ignorerer alle bortsett fra numeriske verdier, mens sammenligningsoperatorene i vår formel returnerer boolske verdier (TRUE / FALSE), som ikke er numeriske. For å konvertere disse boolske verdiene til 1 «s og 0» s, bruker du det dobbelte minustegnet, som teknisk sett kalles den dobbelte unary-operatøren. Den første unary tvinger SANN / FALSK til henholdsvis -1/0. Den andre unaryen negerer verdiene, dvs. reverserer tegnet og gjør dem til +1 og 0, som SUMPRODUCT-funksjonen kan forstå.
Jeg håper forklaringen ovenfor gir mening. Og selv om det ikke gjør det, husk bare denne tommelfingerregelen – bruk den doble unary-operatoren (-) når du bruker sammenligningsoperatorer i SUMPRODUCT-formlene.
Bruk av Excel SUM i matriseformler
Som du husker, implementerte Microsoft SUMIFS-funksjonen i Excel 2007. Hvis noen fremdeles bruker Excel 2003, 2000 eller tidligere, må du bruke en SUM-matriseformel for å legge til verdier med flere OG-kriterier. fungerer i moderne versjoner av Excel 2013 – 2007 også, og kan betraktes som en gammeldags motstykke til SUMIFS-funksjonen.
I SUMIF-formlene diskutert ovenfor har du allerede brukt arrayargumenter, men en arrayformel er noe annerledes.
Eksempel 1. Summen med flere OG-kriterier i Excel 2003 og tidligere
La oss komme tilbake til det aller første eksemplet der vi fant ut en sum av beløp relatert til til en gitt frukt og leverandør:
Som du allerede vet, blir denne oppgaven enkelt utført ved hjelp av en vanlig SUMIFS-formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Og la oss nå se hvordan den samme oppgaven kan oppfylles i tidlige «SUMIFS-frie» versjoner av Excel . For det første skriver du ned alle betingelsene som skal oppfylles i form av område = «betingelse». I dette eksemplet har vi to rekkevidde / tilstandspar:
Tilstand 1: A2: A9 = «epler»
Tilstand 2: B2: B9 = «Pete»
Deretter skriver du en SUM-formler som «multipliserer» alle områdene / tilstandsparene dine, hver lukket i parentes. Den siste multiplikatoren er sumområdet, C2: C9 i vårt tilfelle:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Som illustrert i skjermbildet nedenfor, er formelen fungerer perfekt i den siste versjonen av Excel 2013.
Eksempel 2. SUM-matriseformler i moderne Excel-versjoner
Even i moderne versjoner av Excel, bør ikke kraften til SUM-funksjonen undervurderes. SUM-matriseformelen er ikke bare sinnets gymnastikk, men har en praktisk verdi, som vist i følgende eksempel.
Anta , har du to kolonner, B og C, og du må telle hvor mange ganger kolonne C er større enn kolonne B, når en verdi i kolonne C er større eller lik 10. En øyeblikkelig løsning som du tenker på er å bruke SUM matriseformel:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Se ikke noe praktisk anvendelse på formelen ovenfor? Tenk på det på en annen måte 🙂
Anta at du har ordrelisten som vist på skjermbildet nedenfor, og du vil vite hvor mange produkter som ikke har blitt levert i sin helhet innen en gitt dato. Oversatt til Excel sitt språk har vi følgende betingelser:
Betingelse 1: En verdi i kolonne B (bestilte varer) er større enn 0
Tilstand 2: En verdi i kolonne C (levert) i mindre enn i kolonne B
Betingelse 3: En dato i kolonne D (Forfallsdato) er mindre enn 11/1/2014.
Setter de tre områdene / tilstandspar sammen, får du følgende formel:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Vel, formeleksemplene som er diskutert i denne opplæringen, har bare riper på overflaten av hva Excel SUMIFS og SUMIF-funksjonene virkelig kan gjøre. Men forhåpentligvis har de hjulpet å peke deg i riktig retning, og nå kan du oppsummere verdier i Excel-arbeidsbøkene dine uansett hvor mange intrikate forhold du må vurdere.
- Hvordan bruke SUMIF i Excel – formeleksempler for betinget summering av celler
- Hvordan bruke Excel SUMIFS og SUMIF med flere kriterier – formeleksempler
- Slik bruker du VLOOKUP & SUM- eller SUMIF-funksjoner i Excel
- SUMIF i Google Sheets med formeleksempler
- COUNTIF i Excel – tell hvis ikke blank, større enn, duplikat eller unik
- Hvordan telle og summere celler etter farge i Excel