1. Functia Excel COUNTIFS – sintaxa si utilizare

Functia Excel COUNTIFS numara celulele dintr-un tabel Excel in functie de una sau mai multe conditii logice date.

Functia este disponibila in Excel Office 365, 2019, 2016, 2013, 2010 si 2007, astfel incat puteti utiliza exemplele de mai jos in oricare dintre versiunile Excel.

Sintaxa functiei COUNTIFS este urmatoarea:

COUNTIFS (criterii_range1, criterii1, [criterii_range2, criterii2] …)

criterii_range1 – defineste setul de celule la care se aplica prima conditie (criterii1).

criterii1 – stabileste conditia logica de cautare ce se aplica pe criterii_range1. Criteriul defineste ce celule trebuie numarate si pot fi exprimate ca 15, „>=150”, A12, „legume” etc.

[criterii_range2, criterii2] … – acestea sunt seturi de celule optionale si criteriile asociate lor. Puteti specifica pana la 127 de perechi de intervale / criterii in formulele dvs.

Un prim exemplu simplu, priviti tabelul de mai jos:

=COUNTIFS(B2:B13, „Cluj”, D2:D13, „Carlota”)

2. Cum se numara celule cu mai multe criterii de indeplinit simultan (AND logic)

Exemplul 2.1: Cate produse „Aspen” cau Costul mai mare de 20 (2 conditii logice)?

=COUNTIFS(D2:D13, „Aspen”, E2:E13, „>20”)

Exemplul 2.2: Cate produse „Carlota” din Cluj cau Costul mai mare de 100 (3 conditii logice)?

=COUNTIFS(D2:D13, „Carlota”, B2:B13,”Cluj”,E2:E13, „>100”)

Exemplul 2.3: Cate Costuri situate in intervalul 50 si 100 exista (2 conditii logice)?

=COUNTIFS(D2:D13, „Carlota”, B2:B13,”Cluj”,E2:E13, „>100”)

Exemplul 2.4 Criterii logice cu valorile cautate puse ca referinte in Excel.

Luam ultimul exemplu de mai sus (2.3). Acel interval 50-100, nu dorim sa-l modificam manual in formula de fiecare data cand se schimba.

Vom pune cele doua numere din interval in doua celule separate, vezi mai jos:

=COUNTIFS(E2:E13, „>=” & G6, E2:E13, „<=” & H6)

3. Cum se numara celule cu mai multe criterii de indeplinit separat (OR logic)

4. COUNTIFS cu metacaractere (wildcards) pentru numararea textelor partiale

Cum se utilizeaza COUNTIFS cu metacaractere (wildcard):

In formulele ce folosesc COUNTIFS, puteti utiliza urmatoarele caractere cu caracter special (de inlocuire) pentru a gasi textele partiale:

Semn de intrebare (?) – inlocuieste orice succesiune de caractere, folositi-l pentru a numara celule care incep si / sau se termina cu anumite caractere.

Asterisk (*) – se potriveste cu orice secventa de caractere, il utilizati pentru a numara celule care contin un cuvant specificat sau un caracter (e) ca parte a continutului celulei.

Sfat. Daca doriti sa numarati celulele ce contin chiar unul dintre metacaracterele de mai sus (adica un semn de intrebare sau un asterisk), introduceti un simbol tilda (~) inainte de un asterisc sau un semn de intrebare (exemplu mai jos)

Cateva exemple concrete, se da tabelul de mai jos cu urmatoarele enunturi de probleme:

Cate tipuri de mere exista in Bucuresti?

=COUNTIFS(B2:B13, „*mere*”,A2:A13, „Bucuresti”)

Cate produse ce incep cu litera „m” din Arad’?

=COUNTIFS(B2:B13, „m*”, A2:A13, „Arad”)

Cate produse se termina in „verzi” din Timisoara?

=COUNTIFS(B2:B13, „*verzi”, A2:A13, „Timisoara”)

Cate produse de tipul: COD:XXXXX exista in orasele ce incep cu litera A?

=COUNTIFS(B2:B13, „COD:?????”, A2:A13, „A*”)

Cate produse contin in denumire semnul intrebarii „?”

=COUNTIF(A2:A12, „*~?*”)

 pentru ca este o singura conditie logica de indeplinit, in acest caz am inlocuit functia COUNTIFS cu COUNTIF

5. Cum se numara datele calendaristice in functie de mai multe criterii

Cum numaram datele calendaristice dintr-un interval?

Cate facturi au fost emise in intervalul 1 Feb 2020 si 1 Martie 2020?

=COUNTIFS(B2:B13,”>=2/1/2020″, B2:B13, „<=3/1/2020”)

Formula de mai sus functioneaza doar daca datele au fost scrise conform cu setarile regionale ale comupterului (in acest caz USA)

Varianta profesionista de a ne referi la datele calendaristice scrise manual?

=COUNTIFS(B2:B13,”>=” & Date(2020, 2, 1), B2:B13, „<=” & Date(2020,3,1))

Folosim functia Date pentru a pasa datele calendaristice indiferent de setarile regionale

Cate facturile emise intr-un intervalul specificat intr-un panou de control?

=COUNTIFS(B2:B13,”>=” &F7, B2:B13, „<=” & G7)

Daca datele calendaristice sunt scrise in alte celule, nu mai trebuie sa folosim functia Date

6. Cum numaram toate valorile unice sau duplicate de pe o coloana in Excel?

Exemplul 6.1: Cate valori unice exista pe coloana: „Aici numar valorile unice”:

{=SUM(IF(COUNTIF(A2:A11,A2:A11)=1,1,0))}

Formula de mai sus trebuie introdusa cu CTRL+SHIFT+ENTER (nu scrieti manual acoladele pentru ca nu va functiona)

Dupa cum vedeti, trei functii diferite sunt utilizate in formula noastra de valori unice – SUM, IF si COUNTIF. Privind din interior spre exterior, iata ce face fiecare functie:

Functia COUNTIF contorizeaza de cate ori apare fiecare valoare individuala in intervalul specificat.

Pasul 1: COUNTIF(A2:A11,A2:A11) returneaza vectorul {3;2;3;1;1;2;2;1;3;2}

Pasul 2: Apoi functia IF evalueaza fiecare valoare din tabloul returnat de COUNTIF, pastreaza toate valorile 1 (cele unice) si inlocuieste toate celelalte valori cu zerouri. Iata ce retuneaza IF(COUNTIF(A2:A11,A2:A11)=1,1,0) : {0;0;0;1;1;0;0;1;0;0}

Pasul 3: Ultima functie executata este SUM, care aduna valorile din tabloul returnat de IF si produce numarul total de valori unice

Sfat. Pentru a vedea la ce evalueaza o anumita parte din formula dvs. de valori unice Excel, selectati acea parte in bara de formule si apasati tasta F9.

Exemplul 6.2: Cate valori unice de tip TEXT exista pe coloana: „Aici numar valorile unice”:

{=SUM(IF(ISTEXT(A2:A11)*COUNTIF(A2:A11,A2:A11)=1,1,0))}

Formula de mai sus trebuie introdusa cu CTRL+SHIFT+ENTER (nu scrieti manual acoladele pentru ca nu va functiona)

Formula returneaza numarul total de valori de text unice, excluzand celulele goale sau BLANK, numerele, valorile logice TRUE si FALSE si erorile.

Exemplul 6.3: Cum numar valorile unice de tip NUMERIC:

Pentru a numara valorile numerice unice dintr-o lista de date, utilizati o formula vectoriala asemanatoare cu cea din exemplul cu valorile unice de tip text, cu singura diferenta ca vom folosi functia ISNUMBER in loc de ISTEXT

{=SUM(IF(ISNUMBER(A2:A11)*COUNTIF(A2:A11,A2:A11)=1,1,0))}

Formula de mai sus trebuie introdusa cu CTRL+SHIFT+ENTER (nu scrieti manual acoladele pentru ca nu va functiona)

Sper ca ati gasit aceste exemple de numarat cu criterii multiple utile.

Oricum, va multumesc ca ati citit si sper sa ne revedem pe blog saptamana viitoare!