Excel har många funktioner där en användare behöver ange ett eller flera kriterier för att få resultatet. Om du till exempel vill räkna celler baserat på flera kriterier kan du använda COUNTIF-eller COUNTIFS-funktionerna i Excel.
denna handledning omfattar olika sätt att använda en enda eller flera kriterier i COUNTIF och COUNTIFS funktion i Excel.,
När jag kommer främst att fokusera på ANTAL.OM och COUNTIFS funktioner i denna handledning, alla dessa exempel kan även användas i andra Excel-funktioner för att ta flera kriterier som insatsvaror (som SUMMA.OM, SUMIFS, AVERAGEIF, och AVERAGEIFS).
denna handledning omfattar:
en introduktion till Excel COUNTIF och COUNTIFS funktioner
Låt oss först få grepp om att använda COUNTIF och COUNTIFS funktioner i Excel.,
Excel COUNTIF funktion (tar enstaka kriterier)
Excel COUNTIF funktion är bäst lämpad för situationer när du vill räkna celler baserat på ett enda kriterium. Om du vill räkna baserat på flera kriterier, använd COUNTIFS-funktionen.
Syntax
=COUNTIF(intervall, kriterier)
Inmatningsargument
- range – intervallet av celler som du vill räkna.
- kriterier – de kriterier som måste utvärderas mot cellintervallet för att en cell ska räknas.,
Excel COUNTIFS funktion (tar flera kriterier)
Excel COUNTIFS funktion är bäst lämpad för situationer när du vill räkna celler baserat på flera kriterier.
Syntax
=COUNTIFS (criteria_range1, criteria1, …)
Inmatningsargument
- criteria_range1 – intervallet av celler som du vill utvärdera mot criteria1.
- criteria1 – de kriterier som du vill utvärdera för criteria_range1 för att bestämma vilka celler som ska räknas.
- – intervallet av celler som du vill utvärdera mot kriterier2.,
- – de kriterier som du vill utvärdera för criteria_range2 för att bestämma vilka celler som ska räknas.
låt oss nu ta en titt på några exempel på att använda flera kriterier i COUNTIF-funktioner i Excel.
använda SIFFERKRITERIER i Excel COUNTIF-funktioner
#1 räkna celler när kriterierna är lika med ett värde
för att få antalet celler där kriterieargumentet är lika med ett angivet värde kan du antingen direkt ange kriterierna eller använda cellreferensen som innehåller kriterierna.,
nedan är ett exempel där vi räknar cellerna som innehåller numret 9 (vilket innebär att kriterierargumentet är lika med 9). Här är formeln:
=COUNTIF ($B$2:$B$11,D3)
i ovanstående exempel (i bilden) är kriterierna i cell D3. Du kan också ange kriterierna direkt i formeln., Du kan till exempel också använda:
=COUNTIF($B$2:$b$11,9)
#2 räkna celler när kriterierna är större än ett värde
för att få antalet celler med ett värde som är större än ett angivet värde använder vi det större än operatören (”>”). Vi kan antingen använda den direkt i formeln eller använda en cellreferens som har kriterierna.
När vi använder en operatör i kriterier i Excel måste vi lägga den inom dubbla citat., Om kriterierna till exempel är större än 10 måste vi ange ”>10” som kriterierna (se bilden nedan):
här är formeln:
=COUNTIF($B$2:$B$11,”>10″)
Du kan också ha kriterierna i en cell och använda cellreferensen som kriterier. I det här fallet behöver du inte ange kriterierna i dubbla citat:
=COUNTIF($B$2:$B$11, D3)
det kan också finnas ett fall när du vill att kriterierna ska vara i en cell,men vill inte ha det med operatören., Du kanske till exempel vill att cellen D3 ska ha numret 10 och inte >10.
i så fall måste du skapa ett kriterieargument som är en kombination av operator-och cellreferens (se bilden nedan):
=COUNTIF($B$2:$B$11,”>”&D3)
OBS: När du kombinerar en operatör och en en cellreferens, operatören är alltid i dubbla citat. Operator-och cellreferensen förenas av en ampersand (&).,
#3 räkna celler när kriterierna är mindre än ett värde
för att få antalet celler med ett värde mindre än ett angivet värde använder vi mindre än operatören (”<”). Vi kan antingen använda den direkt i formeln eller använda en cellreferens som har kriterierna.
När vi använder en operatör i kriterier i Excel måste vi lägga den inom dubbla citat., Till exempel, om kriteriet är att numret ska vara mindre än 5, måste vi ange ”<5” som kriterierna (se bilden nedan):
=COUNTIF($B$2:$B$11,”<5″)
Du kan också ange ”< 5”)
ha kriterierna i en cell och använd cellreferensen som kriterierna., I det här fallet behöver du inte ange kriterierna i dubbla citat (se bilden nedan):
=COUNTIF($B$2:$B$11,D3)
det kan också finnas ett fall när du vill att kriterierna ska vara i en cell, men vill inte ha det med operatören. Du kanske till exempel vill att cellen D3 ska ha numret 5 och inte <5.,
i så fall måste du skapa ett kriterieargument som är en kombination av operatörs-och cellreferens:
=COUNTIF($B$2:$B$11,”<”&D3)
OBS: När du kombinerar en operatörsgrupp måste du och en cellreferens, operatören är alltid i dubbla citat. Operator-och cellreferensen förenas av en ampersand (&).,
#4 räkna celler med flera kriterier – mellan två värden
för att få ett antal värden mellan två värden måste vi använda flera kriterier i COUNTIF-funktionen.
här är två metoder för att göra detta:
metod 1: Använda COUNTIFS funktion
COUNTIFS funktion kan hantera flera kriterier som argument och räknar cellerna endast när alla kriterier är sanna., För att räkna celler med värden mellan två angivna värden (säg 5 och 10) kan vi använda följande COUNTIFS-funktion:
=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)
Obs! ovanstående formel räknar inte celler som innehåller 5 eller 10. Om du vill inkludera dessa celler, använd större än lika med (>=) och mindre än lika med (<=) operatorer., Här är formeln:
=COUNTIFS ($B$2:$B$11,”>=5″,$b$2:$B$11,”<=10″)
Du kan också ha dessa kriterier i celler och använda cellreferensen som kriterierna. I det här fallet behöver du inte ange kriterierna i dubbla citat (se bild nedan):
Du kan också använda en kombination av cellreferenser och operatörer (där operatören anges direkt i formeln). När du kombinerar en operatör och en cellreferens är operatören alltid i dubbla citat., Operator-och cellreferensen förenas av en ampersand (&).
metod 2: Använda två COUNTIF-funktioner
om du har flera kriterier kan du antingen använda COUNTIFS eller skapa en kombination av COUNTIF-funktioner., Formeln nedan skulle också göra samma sak:
=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)
i ovanstående formel hittar vi först antalet celler som har ett värde som är större än 5 och vi subtraherar antalet celler med ett värde som är större än 5.10. Detta skulle ge oss resultatet som 5 (vilket är antalet celler som har värden mer än 5 och mindre än lika med 10).,3c219a21b4″>
om du vill att formeln ska inkludera både 5 och 10, använd följande formel istället:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)
om du vill att formeln ska inkludera både 5 och 10, använd följande formel istället:
=COUNTIF($B$2:$B$11,”>10″)vill du att formeln ska utesluta både ’5’ och ’10’ från räkningen, använd följande formel:
=COUNTIF($B$2:$B$11,”> = 5″)-COUNTIF($B$2:$B$11, ”> 10”)-COUNTIF ($B $ 2: $ b $ 11,10)
Du kan ha dessa kriterier i celler och använda cellreferenser, eller så kan du använda en kombination av operatörer och celler referenser.,
använda Textkriterier i Excel-funktioner
#1 räkna celler när kriterierna är lika med en angiven text
för att räkna celler som innehåller en exakt matchning av den angivna texten, kan vi helt enkelt använda den texten som kriterierna. Till exempel, i datauppsättningen (visas nedan i bilden), om jag vill räkna alla celler med namnet Joe i det, kan jag använda nedanstående formel:
=COUNTIF($B$2:$B$11,”Joe”)
eftersom det här är en textsträng måste jag sätta textkriterierna i dubbla citat.,
Du kan också ha kriterierna i en cell och sedan använda den cellreferensen (som visas nedan):
=COUNTIF($B$2:$B$11,E3)
OBS: Du kan få felaktiga resultat om det finns Ledande/avslutande utrymmen i kriterierna eller kriterier intervall. Se till att du rengör data innan du använder dessa formler.
#2 räkna celler när kriterierna inte är lika med en angiven text
liknar vad vi såg i exemplet ovan, kan du också räkna celler som inte innehåller en angiven text., För att göra detta måste vi använda not equal to operator (<>).
Antag att du vill räkna alla celler som inte innehåller namnet JOE, här är formeln som kommer att göra det:
=COUNTIF($B$2:$B$11,”<>Joe”)
Du kan också räkna alla celler som inte innehåller namnet JOE.ha kriterierna i en cell och använd cellreferensen som kriterierna., I det här fallet behöver du inte ange kriterierna i dubbla citat (se bilden nedan):
=COUNTIF($B$2:$B$11,E3)
det kan också finnas ett fall när du vill att kriterierna ska vara i en cell men inte vill ha det med operatören. Du kanske till exempel vill att cellen D3 ska ha namnet Joe och inte <>Joe.,
i så fall måste du skapa ett kriterieargument som är en kombination av operatörs-och cellreferens (se bilden nedan):
=COUNTIF($B$2:$B$11,”<>”&E3)
&E3)
När du kombinerar en operatör och en cellreferens är operatören alltid i dubbla citat. Operator-och cellreferensen förenas av en ampersand (&).
använda datum kriterier i Excel COUNTIF och COUNTIFS funktioner
Excel lagra datum och tid som nummer., Så vi kan använda det på samma sätt som vi använder siffror.
#1 räkna celler när kriterierna är lika med ett angivet datum
för att få antalet celler som innehåller det angivna datumet, skulle vi använda equal to operator ( = ) tillsammans med datumet.
för att använda datumet rekommenderar jag att du använder DATUMFUNKTIONEN, eftersom det blir av med eventuella fel i datumvärdet. Så, till exempel, om jag vill använda datumet 1 September 2015, Kan jag använda DATUMFUNKTIONEN som visas nedan:
=datum (2015,9,1)
denna formel skulle returnera samma datum trots regionala skillnader., Till exempel skulle 01-09-2015 vara September 1, 2015 enligt US date syntax och januari 09, 2015 enligt UK date syntax. Denna formel skulle dock alltid återvända den 1 September 2105.
här är formeln för att räkna antalet celler som innehåller datumet 02-09-2015:
=COUNTIF($a$2:$A$11,DATE(2015,9,2))
#2 räkna celler när kriterierna är före eller efter ett angivet datum
för att räkna celler som innehåller datum före eller efter ett angivet datum kan vi använda den mindre än/större än operatörerna.,
om jag till exempel vill räkna alla celler som innehåller ett datum som är efter september 02, 2015, Kan jag använda formeln:
=COUNTIF($a$2:$a$11,”>”&datum(2015,9,2))
På samma sätt kan du också räkna antalet celler före ett angivet datum. Om du vill inkludera ett datum i räkningen, använd och ”lika med” operatör tillsammans med ”större än/mindre än” operatör.
Du kan också använda en cellreferens som innehåller ett datum., I det här fallet måste du kombinera operatören (inom dubbla citat) med datumet med en ampersand (&).
se exempel nedan:
=COUNTIF($a$2:$a$11,”>”&F3)
#3 räkna celler med flera kriterier – mellan två datum
för att få en räkning av värden mellan två värden måste vi använda flera kriterier i COUNTIF-funktionen.
Vi kan göra detta med två metoder – en enda COUNTIFS-funktion eller två COUNTIF-funktioner.,
metod 1: med COUNTIFS funktion
COUNTIFS funktion kan ta flera kriterier som argument och räknar cellerna endast när alla kriterier är sanna. För att räkna celler med värden mellan två angivna datum (säg September 2 och September 7) kan vi använda följande COUNTIFS-funktion:
=COUNTIFS($a$2:$a$11,”>”&datum(2015,9,2),$a$2:$a$11,”<”&datum(2015,9,7))
ovanstående formel räknar inte celler som innehåller de angivna datumen., Om du också vill inkludera dessa datum, använd mer än lika med (>=) och mindre än lika med (<=) operatorer. Här är formeln:
=COUNTIFS($a$2:$a$11,”>=”&datum(2015,9,2),$a$2:$a$11,”<=”&datum(2015,9,7))
Du kan också ha datumen i en cell och använda cellreferensen som kriterier. I det här fallet kan du inte ha operatören med datumet i cellerna., Du måste manuellt lägga till operatörer i formeln (i dubbla citat) och lägga till cellreferens med en ampersand (&). Se bilden nedan:
=COUNTIFS($a$2:$a$11,”>”&F3,$A$2:$a$11,”<”&G3)
metod 2: Använda COUNTIF-funktioner
om du har flera kriterier kan du antingen använda en countifs-funktion eller skapa en kombination av två COUNTIF-funktioner., Formeln nedan skulle också göra tricket:
=COUNTIF($a$2:$a$11,”>”&datum(2015,9,2))-COUNTIF($a$2:$a$11,”>”&datum(2015,9,7))
i ovanstående formel hittar vi först antalet celler som har ett datum efter den 2 september och vi subtraherar antalet celler med datum efter den 7 september. Detta skulle ge oss resultatet som 7(vilket är antalet celler som har datum efter den 2 September och den 7 September).,>Datum(2015,9,2))-COUNTIF($a$2:$a$11,”>”&datum(2015,9,7))
om du vill utesluta båda datumen från att räkna, använd följande formel:
=COUNTIF($a$2:$A$11,”>”&datum(2015,9,2))-COUNTIF($a$2:$a$11,”>”&datum(2015,9,7)-COUNTIF($a$2:$A$11,datum(2015,9,7)))
Du kan också ha kriteriedatum i celler och använda cellreferenserna (tillsammans med operatörer i dubbla citat kopplade med Ampersand).,
använda jokertecken i kriterier i COUNTIF& COUNTIFS funktioner
det finns tre jokertecken i Excel:
Du kan använda COUNTIF-funktionen med jokertecken för att räkna celler när andra inbyggda räkna funktionen misslyckas. Anta till exempel att du har en datauppsättning som visas nedan:
låt oss nu ta olika exempel:
#1 räkna celler som innehåller Text
för att räkna celler med text i den kan vi använda jokertecknet * (asterisk)., Eftersom asterisk representerar ett antal tecken, skulle det räkna alla celler som har någon text i den. Här är formeln:
= COUNTIFS ($C$2:$C$11,”*”)
notera: formeln ovan ignorerar celler som innehåller siffror, tomma celler och logiska värden, men skulle räkna cellerna innehåller en apostrof (och därmed visas tom) eller celler som innehåller tom sträng ( = ”” ) som kan ha returnerats som en del av en formel.
här är en detaljerad handledning om hantering av fall där det finns en tom sträng eller apostrof.,
här är en detaljerad handledning om hantering av fall där det finns tomma strängar eller apostrofer.
nedan är en video som förklarar olika scenarier för att räkna celler med text i den.
#2 Räkna icke-tomma celler
om du funderar på att använda COUNTA-funktionen, tänk om.
prova det och det kan misslyckas dig. COUNTA kommer också att räkna en cell som innehåller en tom sträng (ofta returneras med formler som =”” eller när människor anger endast en apostrof i en cell). Celler som innehåller tomma strängar ser tomma ut men inte, och räknas därmed av COUNTA-funktionen.,
COUNTA räknar också en cell som innehåller en tom sträng (som ofta returneras med formler som =”” eller när personer endast anger en apostrof i en cell). Celler som innehåller tomma strängar ser tomma ut men inte, och räknas därmed av COUNTA-funktionen.
så om du använder formeln =COUNTA(A1:A11) returnerar den 11, medan den ska returnera 10.
här är fix:
=COUNTIF($A$1:$A$11,”?* ”)+COUNT($a$1:$A$11) + SUMPRODUCT(–ISLOGISK($A$1:$A$11))
låt oss förstå denna formel genom att bryta ner den:
- COUNTIF($N$8:$N$18,”?,* ”) – Den här delen av formeln returnerar 5. Detta inkluderar alla celler som har ett texttecken i det. En ? representerar ett tecken och * representerar valfritt antal tecken. Därav kombinationen ?* i kriterierna tvingar excel att räkna celler som har minst ett texttecken i den.
- räkna($A$1:$A$11) – Detta räknar alla celler som innehåller siffror. I ovanstående exempel returnerar den 3.
- SUMPRODUCT(–ISLOGICAL($A$1:$A$11) – Detta räknar alla celler som innehåller logiska värden. I ovanstående exempel returnerar den 2.,
#3 räkna celler som innehåller specifik text
låt oss säga att vi vill räkna alla celler där försäljning rep namn börjar med J. detta kan lätt uppnås genom att använda ett jokertecken i COUNTIF funktion. Här är formeln:
=COUNTIFS ($C$2:$C$11,”J*”)
kriterierna J* anger att texten i en cell ska börja med J och kan innehålla valfritt antal tecken.
om du vill räkna celler som innehåller alfabetet var som helst i texten, flankera det med en asterisk på båda sidor., Om du till exempel vill räkna celler som innehåller alfabetet ”A” i det, Använd *A* som kriterier.