Cum se utilizează mai multe criterii în Excel COUNTIF și COUNTIFS

Excel are multe funcții în care un utilizator trebuie să specifice un criteriu unic sau mai multe pentru a obține rezultatul. De exemplu, dacă doriți să numărați celule pe baza mai multor criterii, puteți utiliza funcțiile COUNTIF sau COUNTIFS în Excel.

Acest tutorial acoperă diferite moduri de utilizare a unui criteriu unic sau mai mult în funcția COUNTIF și COUNTIFS în Excel.

În timp ce mă voi concentra în principal pe funcțiile COUNTIF și COUNTIFS în acest tutorial, toate aceste exemple pot fi folosite și în alte funcții Excel care iau mai multe criterii ca intrări (cum ar fi SUMIF, SUMIFS, AVERAGEIF și AVERAGEIFS).

Introducere în funcțiile Excel COUNTIF și COUNTIFS

În primul rând, să analizăm utilizarea funcțiilor COUNTIF și COUNTIFS în Excel.

Funcția Excel COUNTIF (acceptă criterii unice)

Funcția Excel COUNTIF este cea mai potrivită pentru situațiile în care doriți să numărați celule pe baza unui singur criteriu. Dacă doriți să contați pe baza mai multor criterii, utilizați funcția COUNTIFS.

Sintaxă

= COUNTIF (interval, criterii)

Argumente de intrare

• gamă - gama de celule pe care doriți să le numărați.
• criterii - criteriile care trebuie evaluate în raport cu intervalul de celule pentru ca o celulă să fie numărată.

Funcția Excel COUNTIFS (acceptă mai multe criterii)

Funcția Excel COUNTIFS este cea mai potrivită pentru situațiile în care doriți să numărați celule pe baza mai multor criterii.

Sintaxă

= COUNTIFS (interval_criterii1, criterii1, [interval_criterii2, criterii2] …)

Argumente de intrare

• gama_criteriilor1 - Gama de celule pentru care doriți să evaluați în funcție de criteriile1.
• criterii1 - criteriile pe care doriți să le evaluați pentru criteriul_interval1 pentru a determina ce celule să contați.
• [gama_criterii2] - Gama de celule pentru care doriți să evaluați în funcție de criterii2.
• [criterii2] - criteriile pe care doriți să le evaluați pentru domeniul_criterii2 pentru a determina ce celule să contați.

Acum, aruncăm o privire la câteva exemple de utilizare a mai multor criterii în funcțiile COUNTIF în Excel.

Utilizarea criteriilor NUMBER în funcțiile Excel COUNTIF

# 1 Numărați celulele atunci când criteriile sunt EGALE pentru o valoare

Pentru a obține numărul de celule în care argumentul criteriilor este egal cu o valoare specificată, puteți introduce direct criteriile sau puteți utiliza referința celulei care conține criteriile.

Mai jos este un exemplu în care numărăm celulele care conțin numărul 9 (ceea ce înseamnă că argumentul criteriului este egal cu 9). Iată formula:

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

În exemplul de mai sus (în imagine), criteriul se află în celula D3. De asemenea, puteți introduce criteriile direct în formulă. De exemplu, puteți utiliza și:

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

# 2 Numărați celulele atunci când criteriile sunt MAI MULTE decât o valoare

Pentru a obține numărul de celule cu o valoare mai mare decât o valoare specificată, folosim operatorul mai mare decât („>”). Am putea să-l folosim direct în formulă sau să folosim o referință de celulă care are criteriile.

Ori de câte ori folosim un operator în criterii în Excel, trebuie să îl plasăm în ghilimele duble. De exemplu, dacă criteriile sunt mai mari de 10, atunci trebuie să introducem „> 10” drept criteriu (a se vedea imaginea de mai jos):

Iată formula:

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

De asemenea, puteți avea criteriile într-o celulă și puteți utiliza referința celulei ca criterii. În acest caz, NU trebuie să puneți criteriile în ghilimele duble:

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

Ar putea exista, de asemenea, un caz în care doriți ca criteriile să se afle într-o celulă, dar nu doriți acest lucru cu operatorul. De exemplu, poate doriți ca celula D3 să aibă numărul 10 și nu> 10.

În acest caz, trebuie să creați un argument criteriu care să fie o combinație de referință operator și celulă (a se vedea imaginea de mai jos):

= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)

NOTĂ: Când combinați un operator și o referință de celulă, operatorul este întotdeauna între ghilimele duble. Operatorul și referința celulei sunt alăturate de un ampersand (&).

# 3 Numărați celulele atunci când criteriile sunt mai mici decât o valoare

Pentru a obține numărul de celule cu o valoare mai mică decât o valoare specificată, folosim operatorul mai puțin decât („<“). Am putea să-l folosim direct în formulă sau să folosim o referință de celulă care are criteriile.

Ori de câte ori folosim un operator în criterii în Excel, trebuie să îl plasăm în ghilimele duble. De exemplu, dacă criteriul este ca numărul să fie mai mic de 5, atunci trebuie să introducem „<5” drept criteriu (a se vedea imaginea de mai jos):

= COUNTIF ($ B $ 2: $ B $ 11, „<5 ″)

De asemenea, puteți avea criteriile într-o celulă și puteți utiliza referința celulei ca criterii. În acest caz, NU trebuie să puneți criteriile în ghilimele duble (a se vedea imaginea de mai jos):

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

De asemenea, ar putea exista un caz în care doriți ca criteriile să se afle într-o celulă, dar nu doriți acest lucru cu operatorul. De exemplu, poate doriți ca celula D3 să aibă numărul 5 și nu <5.

În acest caz, trebuie să creați un argument criteriu care să fie o combinație de referință operator și celulă:

= COUNTIF ($ B $ 2: $ B $ 11, ”<“ & D3)

NOTĂ: Când combinați un operator și o referință de celulă, operatorul este întotdeauna între ghilimele duble. Operatorul și referința celulei sunt alăturate de un ampersand (&).

# 4 Numărați celulele cu mai multe criterii - între două valori

Pentru a obține un număr de valori între două valori, trebuie să utilizăm mai multe criterii în funcția COUNTIF.

Iată două metode de a face acest lucru:

METODA 1: Utilizarea funcției COUNTIFS

Funcția COUNTIFS poate gestiona mai multe criterii ca argumente și numără celulele numai atunci când toate criteriile sunt ADEVĂRATE. Pentru a număra celule cu valori cuprinse între două valori specificate (de exemplu 5 și 10), putem utiliza următoarea funcție COUNTIFS:

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

NOTĂ: Formula de mai sus nu ia în considerare celulele care conțin 5 sau 10. Dacă doriți să includeți aceste celule, utilizați operatorii mai mari decât egali cu (> =) și mai mici decât egali cu (<=). Iată formula:

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

De asemenea, puteți avea aceste criterii în celule și puteți utiliza referința celulei ca criterii. În acest caz, NU trebuie să puneți criteriile în ghilimele duble (a se vedea imaginea de mai jos):

De asemenea, puteți utiliza o combinație de referințe de celule și operatori (unde operatorul este introdus direct în formulă). Când combinați un operator și o referință de celulă, operatorul este întotdeauna între ghilimele duble. Operatorul și referința celulei sunt alăturate de un ampersand (&).

METODA 2: Utilizarea a două funcții COUNTIF

Dacă aveți mai multe criterii, puteți folosi COUNTIFS sau puteți crea o combinație de funcții COUNTIF. Formula de mai jos ar face, de asemenea, același lucru:

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

În formula de mai sus, găsim mai întâi numărul de celule care au o valoare mai mare de 5 și scădem numărul de celule cu o valoare mai mare de 10. Acest lucru ne-ar da rezultatul ca 5 (care este numărul de celule care au valori mai mari de 5 și mai mici decât egale cu 10).

Dacă doriți ca formula să includă atât 5 cât și 10, utilizați în schimb următoarea formulă:

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

Dacă doriți ca formula să excludă atât „5”, cât și „10” din numărare, utilizați următoarea formulă:

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

Puteți avea aceste criterii în celule și puteți utiliza referințele celulelor sau puteți utiliza o combinație de operatori și referințe celule.

Utilizarea criteriilor TEXT în funcțiile Excel

# 1 Numărați celulele atunci când criteriile sunt EGALE pentru un text specificat

Pentru a număra celulele care conțin o potrivire exactă a textului specificat, putem folosi textul respectiv drept criteriu. De exemplu, în setul de date (prezentat mai jos în imagine), dacă vreau să număr toate celulele cu numele Joe în el, pot folosi formula de mai jos:

= COUNTIF ($ B $ 2: $ B $ 11, „Joe”)

Deoarece acesta este un șir de text, trebuie să pun criteriile de text între ghilimele duble.

Puteți avea, de asemenea, criteriile într-o celulă și apoi utilizați acea referință de celulă (așa cum se arată mai jos):

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

NOTĂ: Puteți obține rezultate greșite dacă există spații de conducere / de urmărire în criterii sau în intervalul de criterii. Asigurați-vă că curățați datele înainte de a utiliza aceste formule.

# 2 Numărați celulele atunci când criteriile NU SUNT EGALE cu un text specificat

Similar cu ceea ce am văzut în exemplul de mai sus, puteți număra și celule care nu conțin un text specificat. Pentru a face acest lucru, trebuie să folosim cel care nu este egal cu operatorul ().

Să presupunem că doriți să numărați toate celulele care nu conțin numele JOE, iată formula care o va face:

= COUNTIF ($ B $ 2: $ B $ 11, „Joe”)

De asemenea, puteți avea criteriile într-o celulă și puteți utiliza referința celulei ca criterii. În acest caz, NU trebuie să puneți criteriile în ghilimele duble (a se vedea imaginea de mai jos):

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

Ar putea exista, de asemenea, un caz în care doriți ca criteriile să se afle într-o celulă, dar nu doriți acest lucru cu operatorul. De exemplu, poate doriți ca celula D3 să aibă numele Joe și nu Joe.

În acest caz, trebuie să creați un argument criteriu care să fie o combinație de referință operator și celulă (a se vedea imaginea de mai jos):

= COUNTIF ($ B $ 2: $ B $ 11, ”” & E3)

Când combinați un operator și o referință de celulă, operatorul este întotdeauna între ghilimele duble. Operatorul și referința celulei sunt alăturate de un ampersand (&).

Utilizarea criteriilor DATE în funcțiile Excel COUNTIF și COUNTIFS

Data și ora magazinului Excel ca numere. Deci îl putem folosi la fel cum folosim numerele.

# 1 Numărați celulele atunci când criteriile sunt EGALE la o dată specificată

Pentru a obține numărul de celule care conțin data specificată, am folosi operatorul egal cu = () împreună cu data.

Pentru a utiliza data, vă recomand să utilizați funcția DATE, deoarece scapă de orice posibilitate de eroare în valoarea datei. De exemplu, dacă vreau să folosesc data de 1 septembrie 2015, pot folosi funcția DATE așa cum se arată mai jos:

= DATA (2015,9,1)

Această formulă ar reveni la aceeași dată, în ciuda diferențelor regionale. De exemplu, 01-09-2015 ar fi 1 septembrie 2015 conform sintaxei datei din SUA și 09 ianuarie 2015 conform sintaxei datei din Marea Britanie. Cu toate acestea, această formulă ar reveni întotdeauna la 1 septembrie 2105.

Iată formula de numărare a numărului de celule care conțin data 02-09-2015:

= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))

# 2 Numărați celulele atunci când criteriile sunt înainte sau după o dată specificată

Pentru a număra celulele care conțin data înainte sau după o dată specificată, putem folosi operatorii mai mici decât / mai mari.

De exemplu, dacă vreau să număr toate celulele care conțin o dată care este după 02 septembrie 2015, pot folosi formula:

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

În mod similar, puteți număra și numărul de celule înainte de o dată specificată. Dacă doriți să includeți o dată în numărare, utilizați și operatorul „egal cu” împreună cu operatorul „mai mare decât / mai mic decât”.

De asemenea, puteți utiliza o referință de celulă care conține o dată. În acest caz, trebuie să combinați operatorul (în ghilimele duble) cu data folosind un semn (&).

Vezi exemplul de mai jos:

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

# 3 Numărați celulele cu mai multe criterii - între două date

Pentru a obține un număr de valori între două valori, trebuie să utilizăm mai multe criterii în funcția COUNTIF.

Putem face acest lucru folosind două metode - O singură funcție COUNTIFS sau două funcții COUNTIF.

METODA 1: Utilizarea funcției COUNTIFS

Funcția COUNTIFS poate lua mai multe criterii ca argumente și numără celulele numai atunci când toate criteriile sunt ADEVĂRATE. Pentru a număra celulele cu valori între două date specificate (de exemplu, 2 septembrie și 7 septembrie), putem utiliza următoarea funcție COUNTIFS:

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

Formula de mai sus nu contorizează celulele care conțin datele specificate. Dacă doriți să includeți și aceste date, utilizați operatori mai mari decât egali cu (> =) și mai mici decât egali cu (<=). Iată formula:

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

De asemenea, puteți avea datele într-o celulă și puteți utiliza referința celulei ca criteriu. În acest caz, nu puteți avea operatorul cu data în celule. Trebuie să adăugați manual operatori în formulă (între ghilimele duble) și să adăugați referințe de celulă folosind un ampersand (&). Vedeți imaginea de mai jos:

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

METODA 2: Utilizarea funcțiilor COUNTIF

Dacă aveți mai multe criterii, puteți utiliza fie o funcție COUNTIFS, fie creați o combinație de două funcții COUNTIF. Formula de mai jos ar face, de asemenea, trucul:

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

În formula de mai sus, găsim mai întâi numărul de celule care au o dată după 2 septembrie și scădem numărul de celule cu date după 7 septembrie. Acest lucru ne-ar da rezultatul ca 7 (care este numărul de celule care au date după 2 septembrie și în sau înainte de 7 septembrie).

Dacă nu doriți ca formula să se numere atât pe 2 septembrie, cât și pe 7 septembrie, utilizați în schimb următoarea formulă:

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

Dacă doriți să excludeți ambele date de la numărare, utilizați următoarea formulă:

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

De asemenea, puteți avea datele criteriilor în celule și puteți utiliza referințele celulelor (împreună cu operatorii între ghilimele duble unite folosind ampersand).

Utilizarea CARACTERELOR WILDCARD în Criterii în funcțiile COUNTIF & COUNTIFS

Există trei caractere wildcard în Excel:

1. * (asterisc) - Reprezintă orice număr de caractere. De exemplu, ex * ar putea însemna excel, excelează, exemplu, expert etc.
2. ? (semnul întrebării) - Reprezintă un singur personaj. De exemplu, Tr? Mp ar putea însemna Trump sau Tramp.
3. ~ (tilde) - Este folosit pentru a identifica un caracter wildcard (~, *,?) În text.

Puteți utiliza funcția COUNTIF cu caractere wildcard pentru a număra celulele atunci când alte funcții de numărare încorporate eșuează. De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos:

Să luăm acum diverse exemple:

# 1 Numărați celulele care conțin text

Pentru a număra celulele cu text în el, putem folosi caracterul wildcard * (asterisc). Deoarece asteriscul reprezintă orice număr de caractere, ar număra toate celulele care conțin text. Iată formula:

= COUNTIFS ($ C $ 2: $ C $ 11, „*”)

Notă: Formula de mai sus ignoră celulele care conțin numere, celule goale și valori logice, dar ar număra că celulele conțin un apostrof (și, prin urmare, apar necompletate) sau celule care conțin șir gol (= ””) care poate fi returnat ca parte a unei formule.

Iată un tutorial detaliat despre tratarea cazurilor în care există un șir gol sau apostrof.

Iată un tutorial detaliat despre tratarea cazurilor în care există șiruri goale sau apostrofe.

Mai jos este un videoclip care explică diferite scenarii de numărare a celulelor cu text în ea.

# 2 Numărați celulele care nu sunt goale

Dacă vă gândiți să utilizați funcția COUNTA, gândiți-vă din nou.

Încercați și s-ar putea să vă eșueze. COUNTA va conta, de asemenea, o celulă care conține un șir gol (adesea returnat de formule ca = ”” sau când oamenii introduc doar un apostrof într-o celulă). Celulele care conțin șiruri goale arată goale, dar nu sunt, și astfel sunt numărate de funcția COUNTA.

COUNTA va conta, de asemenea, o celulă care conține un șir gol (adesea returnat de formule ca = ”” sau când oamenii introduc doar un apostrof într-o celulă). Celulele care conțin șiruri goale arată goale, dar nu sunt, și astfel sunt numărate de funcția COUNTA.

Deci, dacă utilizați formula = COUNTA (A1: A11), acesta returnează 11, în timp ce ar trebui să returneze 10.

Iată soluția:

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

Să înțelegem această formulă descompunându-ne:

• COUNTIF ($ N $ 8: $ N $ 18, „? *”) - Această parte a formulei returnează 5. Aceasta include orice celulă care are un caracter text în ea. A ? reprezintă un caracter și * reprezintă orice număr de caractere. Prin urmare, combinația? * Din criterii forțează excelent să numere celulele care au cel puțin un caracter text în ea.
• COUNT ($ A $ 1: $ A $ 11) - Aceasta contorizează toate celulele care conțin numere. În exemplul de mai sus, returnează 3.
• SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - Aceasta numără toate celulele care conțin valori logice. În exemplul de mai sus, returnează 2.

# 3 Numărați celulele care conțin text specific

Să presupunem că dorim să numărăm toate celulele în care numele reprezentantului de vânzări începe cu J. Acest lucru se poate realiza cu ușurință utilizând un caracter wildcard în funcția COUNTIF. Iată formula:

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

Criteriul J * specifică faptul că textul dintr-o celulă ar trebui să înceapă cu J și poate conține orice număr de caractere.

Dacă doriți să numărați celule care conțin alfabetul oriunde în text, flancați-l cu un asterisc pe ambele părți. De exemplu, dacă doriți să numărați celule care conțin alfabetul „a”, utilizați * a * ca criteriu.

Acest articol este neobișnuit de lung în comparație cu celelalte articole ale mele. Sper că ți-a plăcut. Spune-mi gândurile tale lăsând un comentariu.

De asemenea, puteți găsi utile următoarele tutoriale Excel:

• Numărați numărul de cuvinte din Excel.
• Numărați celulele pe baza culorii de fundal în Excel.
• Cum să însumați o coloană în Excel (5 moduri foarte ușoare)