Slik bruker du flere betingelser i Excel COUNTIF og COUNTIFS

Excel har mange funksjoner hvor brukeren må spesifisere en eller flere betingelser for å få resultatet.For eksempel, hvis du vil telle celler basert på flere kriterier, kan du bruke funksjonene COUNTIF eller COUNTIFS i Excel.

Denne opplæringen dekker ulike måter å bruke enkelt- eller flere kriterier i Excels COUNTIF- og COUNTIFS-funksjoner.

Mens jeg først og fremst vil fokusere på funksjonene COUNTIF og COUNTIFS i denne opplæringen, kan alle disse eksemplene også brukes med andre Excel-funksjoner som tar flere kriterier som input (som SUMIF, SUMIFS, AVERAGEIF og AVERAGEIFS).

Introduksjon til Excel-funksjonene COUNTIF og COUNTIFS

La oss først forstå hvordan du bruker funksjonene COUNTIF og COUNTIFS i Excel.

Excel COUNTIF-funksjon (med ett enkelt kriterium)

Excel COUNTIF-funksjonen fungerer best når du vil telle celler basert på et enkelt kriterium.Hvis du ønsker å telle basert på flere kriterier, bruk COUNTIFS-funksjonen.

syntaks

=ANTALLHVIS(område; tilstand)

Inndataparametere

• omfang -  Området med celler du vil beregne.
• tilstand -Kriterier som må vurderes mot celleområdet som skal telles.

Excel COUNTIFS-funksjon (med flere kriterier)

Excel COUNTIFS-funksjonen fungerer best når du vil telle celler basert på flere kriterier.

syntaks

=ANTALLHVIS(kriterieområde1;kriterie1;[kriterieområde2;kriterie2]...)

Inndataparametere

• kriterier_område1 – Celleområdet du ønsker å evaluere mot kriterium 1.
• kriterier1 – Kriteriet du vil evaluere er criterion_range1 for å bestemme hvilke celler som skal evalueres.
• [criteria_range2] – Celleområdet du ønsker å evaluere mot kriterium 2.
• [kriterier2] – Kriteriet du vil evaluere er criterion_range2 for å bestemme hvilke celler som skal telles.

La oss nå se på noen eksempler på bruk av flere kriterier i Excels COUNTIF-funksjon.

Bruk NUMBER-betingelsen i Excel-funksjonen COUNTIF

#1 Tell celler når kriteriet er lik en verdi

For å få antall celler der betingelsesparameteren er lik den angitte verdien, kan du angi betingelsen direkte eller bruke en cellereferanse som inneholder betingelsen.

Nedenfor er et eksempel hvor vi teller celler som inneholder tallet 9 (som betyr at betingelsesparameteren er lik 9).Her er formelen:

=ANTALLHVIS($B$2:$B$11,D3)

I eksemplet ovenfor (på bildet) er kriteriet i celle D3.Du kan også legge inn betingelser direkte i formler.Du kan for eksempel også bruke:

=ANTALLHVIS($B$2:$B$11,9)

#2 Tell celler når tilstanden er større enn verdien

For å få antallet celler med en verdi større enn en spesifisert verdi, bruker vi større enn-operatoren (">").Vi kan bruke det direkte i formler eller vi kan bruke cellereferanser med betingelser.

Når vi bruker en operatør i en tilstand i Excel, må vi sette den i doble anførselstegn.For eksempel, hvis betingelsen er større enn 10, må vi angi ">10" som betingelsen (se bildet nedenfor):

Her er formelen:

=COUNTIF($B$2:$B$11,">10″)

Du kan også inkludere betingelser i celler og bruke cellereferanser som betingelser.I dette tilfellet trenger du ikke å sette betingelsen i doble anførselstegn:

=ANTALLHVIS($B$2:$B$11,D3)

Dette kan også være tilfelle når du vil at en betingelse skal være i en celle, men ikke vil bruke den med en operator.For eksempel vil du kanskje at tallet i celle D3 skal være 10 i stedet for >10.

I dette tilfellet må du opprette en betinget parameter, som er en kombinasjon av operatør og cellereferanse (se bildet nedenfor):

=ANTALLHVIS($B$2:$B$11,">"&D3)

Merk: Når du kombinerer operatorer og cellereferanser, er operatorene alltid omgitt av doble anførselstegn.Operatorer og cellereferanser er koblet sammen med et og-tegn (&).

#3 Tell celler når kriteriet er mindre enn en verdi

For å få antallet celler med en verdi mindre enn en spesifisert verdi, bruker vi mindre enn-operatoren ("<").Vi kan bruke det direkte i formler eller vi kan bruke cellereferanser med betingelser.

Når vi bruker en operatør i en tilstand i Excel, må vi sette den i doble anførselstegn.For eksempel, hvis kriteriet er at mengden skal være mindre enn 5, må vi angi "<5" som kriterium (se bildet nedenfor):

=ANTALLHVIS($B$2:$B$11,"<5")

Du kan også inkludere betingelser i celler og bruke cellereferanser som betingelser.I dette tilfellet trenger du ikke sette standarden i doble anførselstegn (se bildet nedenfor):

=ANTALLHVIS($B$2:$B$11,D3)

Det kan også hende du vil at tilstanden skal være i cellen, men ikke hos operatøren.For eksempel vil du kanskje at celle D3 skal ha tallet 5 i stedet for <5.

I dette tilfellet må du opprette en betinget parameter som er en kombinasjon av operatør og cellereferanse:

=ANTALLHVIS($B$2:$B$11,"<"&D3)

Merk: Når du kombinerer operatorer og cellereferanser, er operatorene alltid omgitt av doble anførselstegn.Operatorer og cellereferanser er koblet sammen med et og-tegn (&).

#4 Tell celler med flere kriterier – mellom to verdier

For å få tellingen av verdier mellom to verdier, må vi bruke flere betingelser i COUNTIF-funksjonen.

Her er to måter å gjøre dette på:

Metode XNUMX: Bruk COUNTIFS-funksjonen

COUNTIFS-funksjonen kan ta flere forhold som argumenter og telle celler bare hvis alle betingelsene er SANN.For å telle celler med verdier mellom to spesifiserte verdier, for eksempel 5 og 10, kan vi bruke følgende COUNTIFS-funksjon:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Merk: Formelen ovenfor teller ikke celler som inneholder 5 eller 10.Hvis du vil inkludere disse cellene, bruk operatorene større enn eller lik (>=) og mindre enn eller lik (<=).Her er formelen:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Du kan også inkludere disse forholdene i celler og bruke cellereferanser som betingelser.I dette tilfellet trenger du ikke sette standarden i doble anførselstegn (se bildet nedenfor):

Du kan også bruke en kombinasjon av cellereferanser og operatorer (skrive inn operatorer direkte i formler).Når du kombinerer operatorer og cellereferanser, er operatorene alltid omgitt av doble anførselstegn.Operatorer og cellereferanser er koblet sammen med et og-tegn (&).

Metode 2: Bruk to COUNTIF-funksjoner

Hvis du har flere betingelser, kan du bruke COUNTIFS eller lage en kombinasjon av COUNTIFS-funksjoner.Følgende formel vil gjøre det samme:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

I formelen ovenfor finner vi først antall celler med en verdi større enn 5, og trekker deretter fra antall celler med en verdi større enn 10.Dette vil gi oss et resultat på 5 (dvs. med verdier større enn 5 og mindre enn eller lik 10).

Hvis du vil at formelen skal inneholde både 5 og 10, bruk følgende formel i stedet:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Hvis du vil at formelen skal ekskludere "5" og "10" fra tellingen, bruk følgende formel:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Du kan inkludere disse betingelsene i celler og bruke cellereferanser, eller du kan bruke en kombinasjon av operatorer og cellereferanser.

Bruk TEKST-betingelser i Excel-funksjoner

#1 Tell celler når betingelsen er lik spesifisert tekst

For å telle celler som inneholder nøyaktig samsvar med en spesifisert tekst, kan vi ganske enkelt bruke den teksten som en betingelse.For eksempel, i et datasett (vist nedenfor), hvis jeg ønsket å telle alle celler som heter Joe, kunne jeg bruke følgende formel:

=COUNTIF($B$2:$B$11,"Joe")

Siden dette er en tekststreng, må jeg sette tekstbetingelsen i doble anførselstegn.

Du kan også inkludere en betingelse i en celle og deretter bruke den cellereferansen (som vist nedenfor):

=ANTALLHVIS($B$2:$B$11,E3)

Merk: Du kan få feil resultater hvis det er innledende/etterfølgende mellomrom i tilstands- eller tilstandsområdet.Sørg for å rense dataene før du bruker disse formlene.

#2 Tell celler når tilstanden ikke er lik spesifisert tekst

I likhet med det vi så i eksempelet ovenfor, kan du også telle celler som ikke inneholder spesifisert tekst.For å gjøre dette, må vi bruke ikke lik-operatoren (<>).

Forutsatt at du vil telle alle celler som ikke inneholder navnet JOE, her er en formel som kan gjøre dette:

=COUNTIF($B$2:$B$11,"<>Joe")

Du kan også inkludere betingelser i celler og bruke cellereferanser som betingelser.I dette tilfellet trenger du ikke sette standarden i doble anførselstegn (se bildet nedenfor):

=ANTALLHVIS($B$2:$B$11,E3)

Dette kan også være tilfelle når du vil at en betingelse skal være i en celle, men ikke vil bruke den med en operator.For eksempel vil du kanskje at celle D3 skal ha navnet Joe i stedet for <>Joe.

I dette tilfellet må du opprette en betinget parameter, som er en kombinasjon av operatør og cellereferanse (se bildet nedenfor):

=ANTALLHVIS($B$2:$B$11,"<>"&E3)

Når du kombinerer operatorer og cellereferanser, er operatorene alltid omgitt av doble anførselstegn.Operatorer og cellereferanser er koblet sammen med et og-tegn (&).

Bruke DATO-betingelser i Excel-funksjonene COUNTIF og COUNTIFS

Excel lagrer datoer og klokkeslett som tall.Så vi kan bruke det som et tall.

#1 Tell celler når tilstanden er lik spesifisert dato

For å få tellingen av celler som inneholder en spesifisert dato, bruker vi lik operatoren (=) sammen med datoen.

For å jobbe med datoer anbefaler jeg å bruke DATE-funksjonen, da den eliminerer enhver sjanse for at datoverdien er feil.Så, for eksempel, hvis jeg ønsket å bruke datoen 2015. september 9, kunne jeg bruke DATE-funksjonen slik:

=DATO(2015,9,1;XNUMX;XNUMX)

Til tross for de regionale forskjellene, vil denne formelen returnere samme dato.For eksempel vil 01 være 09. september 2015 under amerikansk datosyntaks, og 2015. januar 9 under britisk datosyntaks.Denne formelen kommer imidlertid alltid tilbake 2105. september 9.

Her er formelen for å telle antall celler som inneholder datoen 02-09-2015:

=ANTALLHVIS($A$2:$A$11,DATO(2015,9,2;XNUMX))

#2 Tell celler når tilstanden er før eller etter spesifisert dato

For å telle celler som inneholder datoer før eller etter en spesifisert dato, kan vi bruke mindre enn/større enn-operatorer.

For eksempel, hvis jeg ønsket å telle alle celler som inneholder datoer etter 2015. september 9, kan jeg bruke følgende formel:

=COUNTIF($A$2:$A$11,">"&DATE(2015,9,2;XNUMX))

På samme måte kan du også telle antall celler før en spesifisert dato.Hvis du vil inkludere datoer i tellingen, bruk "lik"-operatoren sammen med "større enn/mindre enn"-operatorene.

Du kan også bruke cellereferanser som inneholder datoer.I dette tilfellet må du bruke et og-tegn (&) for å kombinere operatoren (innenfor doble anførselstegn) med datoen.

Se eksempelet nedenfor:

=COUNTIF($A$2:$A$11,">"&F3)

#3 Tell celler med flere kriterier – mellom to datoer

For å få tellingen av verdier mellom to verdier, må vi bruke flere betingelser i COUNTIF-funksjonen.

Vi kan gjøre dette på to måter - en enkelt COUNTIFS-funksjon eller to COUNTIFS-funksjoner.

Metode XNUMX: Bruk COUNTIFS-funksjonen

COUNTIFS-funksjonen kan ta flere forhold som argumenter og telle celler bare hvis alle betingelsene er SANN.For å telle celler med verdier mellom to spesifiserte datoer, for eksempel 9. september og 2. september, kan vi bruke følgende COUNTIFS-funksjon:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Formelen ovenfor teller ikke cellene som inneholder den angitte datoen.Hvis du også vil inkludere disse datoene, bruk operatorene større enn eller lik (>=) og mindre enn eller lik (<=).Her er formelen:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Du kan også inkludere datoer i celler og bruke cellereferanser som kriterier.I dette tilfellet kan du ikke la operatoren inkludere datoen i cellen.Du må manuelt legge til operatorer i formelen (omsluttet av doble anførselstegn) og bruke et og-tegn (&) for å legge til cellereferanser.Se nedenfor:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Metode 2: Bruk COUNTIF-funksjonen

Hvis du har flere betingelser, kan du bruke en COUNTIFS-funksjon eller lage en kombinasjon av to COUNTIFS-funksjoner.Følgende formel vil også gjøre susen:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

I formelen ovenfor finner vi først antall celler som har dato etter 9. september, og trekker deretter fra antall celler som har dato etter 2. september.Dette vil gi et resultat på 9 (dvs. antall celler med datoer etter 7. september og før 7. september).

Hvis du ikke vil at formelen skal beregne både 9. og 2. september, bruker du i stedet følgende formel:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Hvis du vil ekskludere to datoer fra tellingen, bruk følgende formel:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

I tillegg kan du angi en standarddato i en celle og bruke cellereferanser (og operatorer i doble anførselstegn med og-tegn).

Bruk av jokertegn i betingelsene for funksjonene COUNTIF og COUNTIFS

Det er tre jokertegn i Excel:

1. *(Asterisk) - den representerer et hvilket som helst antall tegn.For eksempel kan ex* bety excel, excels, eksempel, ekspert osv.
2. ? (spørsmålstegn) – Den representerer en enkelt karakter.For eksempel kan Trömp bety Trump eller Tramp.
3. ~ (tilde) - Brukes til å identifisere jokertegn (~, *, ?) i tekst.

Du kan bruke COUNTIF-funksjonen med jokertegn for å telle celler når andre innebygde tellefunksjoner mislykkes.Anta for eksempel at du har et datasett som ser slik ut:

La oss nå ta noen eksempler:

#1 Tell celler som inneholder tekst

For å telle celler med tekst i dem, kan vi bruke jokertegnet * (stjerne).Siden asterisker representerer et hvilket som helst antall tegn, vil den telle alle celler som inneholder tekst.Her er formelen:

=COUNTIFS($C$2:$C$11,"*")

Merk: Formelen ovenfor ignorerer celler som inneholder tall, tomme celler og logiske verdier, men teller celler som inneholder apostrof (og derfor vises tomme) eller som kan ha blitt inkludert som en del av formelen.

Her er en detaljert veiledning om håndtering av saker med tomme strenger eller apostrof.

#2 Tell ikke-tomme celler

Hvis du vurderer å bruke COUNTA-funksjonen, tenk om igjen.

Prøv det, det kan skuffe deg.COUNTA vil også telle celler som inneholder tomme strenger (vanligvis returnert av formler som ="", eller når folk bare skriver inn apostrof i celler).Celler som inneholder tomme strenger ser ut til å være tomme, men er det ikke, så de telles av COUNTA-funksjonen.

COUNTA vil også telle celler som inneholder tomme strenger (vanligvis returnert av formler som ="", eller når folk bare skriver inn apostrof i celler).Celler som inneholder tomme strenger ser ut til å være tomme, men er det ikke, så de telles av COUNTA-funksjonen.

Så hvis du bruker formelen = COUNTA (A1:A11), den returnerer 11 når den skal returnere 10.

Her er løsningen:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

La oss forstå denne formelen ved å bryte den ned:

#3 Tell celler som inneholder spesifikk tekst

Anta at vi ønsker å telle alle cellene der selgernavnet begynner med J.Dette kan enkelt oppnås ved å bruke jokertegn i COUNTIF-funksjonen.Her er formelen:

=COUNTIFS($C$2:$C$11,"J*") 

Betingelsen J* spesifiserer at teksten i cellen skal begynne med J og kan inneholde et hvilket som helst antall tegn.

Hvis du vil telle celler som inneholder bokstaver hvor som helst i teksten, omslutt dem med en stjerne.For eksempel, hvis du vil telle celler som inneholder bokstaven "a", bruk *a* som betingelse.

Sammenlignet med mine andre artikler er denne uvanlig lang.Håper du liker det.Fortell meg hva du synes ved å legge igjen en kommentar.