La funció COUNTIF d'Excel us permet comptar el nombre de cel·les que compleixen criteris o condicions específics en l'interval donat.
La funció COUNTIF és una de les funcions estadístiques d'Excel que és una combinació de funcions COUNT i IF o la funció COUNTA. Quan s'utilitza a la fórmula, la funció compta el nombre de cel·les que coincideixen amb criteris o condicions específics en el mateix o en diversos intervals. La funció COUNTIF ajuda a comptar cel·les que contenen text, números o dates que compleixen uns criteris específics.
Podeu comptar cel·les mitjançant les funcions COUNTIF o COUNTIFS a Excel. La diferència entre les funcions COUNTIF i COUNTIFS és que COUNTIF s'utilitza per comptar cel·les que compleixen un criteri en un interval, mentre que COUNTIFS compta les cel·les que compleixen diverses condicions en el mateix o en diversos intervals.
Aquest article us mostrarà com utilitzar les dues funcions COUNTIF i COUNTIFS a Excel.
Excel COUNTIF Funció
La funció COUNTIF us permet realitzar recomptes de dades en funció d'un criteri o condició específics. La condició utilitzada a la funció funciona amb operadors lògics (, , =, >=, <=) i caràcters comodins (*, ?) per a la concordança parcial.
Sintaxi de la funció COUNTIF
L'estructura d'una funció COUNTIF és:
=CONTARSI(interval,criteri)Paràmetres:
rang– El rang de cel·les a comptar.criteris– La condició determina quines cel·les s'han d'incloure en el recompte de l'interval especificat. Els criteris poden ser un valor numèric, text, referència a una adreça de cel·la o equació.
Ús de la funció COUNTIF per comptar valors numèrics
Com hem comentat anteriorment, els criteris (segon argument) de la funció COUNTIF defineixen la condició que indica a la funció quines cel·les comptar.
Aquesta funció us ajuda a comptar el nombre de cel·les amb valors que compleixen condicions lògiques com ara igual a, major que, menor que o no igual a un valor especificat, etc.
A l'exemple següent, la fórmula compta les cel·les que contenen un valor igual a 5 (criteri). Podeu inserir directament "5 a la fórmula o fer referència a l'adreça de la cel·la que té el valor (cel·la D2 a l'exemple següent).
=COMPTARSI(B2:B11;D2)
La fórmula anterior compta el nombre de cel·les de l'interval de cel·les (B2:B11) que contenen el valor igual al valor de la cel·la D2.
La fórmula següent compta les cel·les que tenen un valor inferior a 5.
=COMPTARSI(B2:B11,"<5")L'operador menys que (<) indica a la fórmula que compti les cel·les amb un valor inferior a "5" a l'interval B2:B11. Sempre que utilitzeu un operador en condicions, assegureu-vos d'incloure cometes dobles ("").
De vegades, quan voleu comptar les cel·les examinant-les amb un criteri (valor) d'una cel·la. En aquests casos, feu un criteri unint un operador i una referència de cel·la. Quan ho feu, heu d'incloure l'operador de comparació entre cometes dobles ("") i, a continuació, col·loqueu un ampersand (&) entre l'operador de comparació i la referència de la cel·la.
=COMPTARSI(B2:B11,">="&D2)
La imatge següent mostra alguns exemples de fórmules i el seu resultat.
Ús de la funció COUNTIF per comptar valors de text
Per comptar cel·les que contenen determinades cadenes de text, utilitzeu aquesta cadena de text com a argument de criteri o la cel·la que conté una cadena de text. Per exemple, a la taula següent, si volem comptar totes les cel·les de l'interval (B21:D27) amb el valor de text de la cel·la B21 (sam), podem utilitzar la fórmula següent:
=COMPTARSI(B21:D27,B21)
Com hem comentat abans, podríem utilitzar el text "sam" directament a la fórmula o utilitzar una referència de cel·la que tingui els criteris (B21). Una cadena de text sempre s'ha de tancar entre cometes dobles ("") quan s'utilitza en una fórmula a Excel.
=COMPTARSI(B21:D27,"sam")
Per comptar cel·les que no contenen un text especificat, utilitzeu la fórmula següent:
=COMPTARSI(B21:D27,""&B21)Assegureu-vos d'adjuntar el "no és igual a" "" operador entre cometes dobles.
Si utilitzeu el text "sam" directament a la fórmula, haureu d'adjuntar l'operador "" i la cadena de text ("sam") entre cometes dobles.
=COMPTARSI(B21:D27,"sam") 
Ús de comodins a la funció COUNTIF d'Excel (concordança parcial)
Podeu utilitzar la fórmula COUNTIF amb caràcters comodís per comptar les cel·les que contenen una paraula, frase o lletres específiques. Hi ha tres caràcters comodí que podeu utilitzar a la funció COUNTIF d'Excel:
*(asterisc): s'utilitza per comptar cel·les amb qualsevol nombre de caràcters/lletres inicials i finals. (p. ex., St* podria significar Stark, Stork, Stacks, etc.?(signe d'interrogació) – S'utilitza per trobar cel·les amb qualsevol caràcter. (per exemple, St?rk podria significar Stark o Stork.~(tilde) – S'utilitza per trobar i comptar el nombre de cel·les que contenen un signe d'interrogació o un asterisc (~, *, ?) al text.
Recompte de cel·les que comencen o acaben amb determinats caràcters
Per comptar les cel·les que comencen o acaben amb un text específic amb qualsevol nombre d'altres caràcters d'una cel·la, utilitzeu un asterisc (*) comodí al segon argument de la funció COUNTIF.
Utilitzeu aquesta fórmula de mostra:
=COMPTARSI(A1:A10,"A*") – per comptar cel·les que comencen per “A”.
=COMPTARSI(A19:A28,"*er") – per comptar el nombre de cel·les que acaben amb els caràcters “er”.
=COMPTARSI(A2:A12,"*QLD*") – per comptar les cel·les que contenen el text "QLD" a qualsevol part de la cadena de text.
A ? representa exactament un caràcter, utilitzeu aquest comodí a la funció COUNTIF a continuació per comptar el nombre de cel·les que contenen exactament +1 caràcter on "?' s'utilitza.
=COMPTARSI(A1:A10,"Par?s")
Recompte de cel·les buides i no buides amb la funció COUNTIF
La fórmula COUNTIF també és útil quan es tracta de comptar el nombre de cel·les buides o no buides en un interval determinat.
Comptar les cèl·lules no en blanc
Si voleu comptar només les cel·les que contenen valors de "text", utilitzeu la fórmula següent. Aquesta fórmula considera les cel·les amb dates i números com a cel·les buides i no les inclourà al recompte.
=COMPTARSI(A1:B12,"*")El comodí * coincideix només amb els valors de text i retorna el recompte de tots els valors de text de l'interval donat.
Si voleu comptar totes les cel·les no buides d'un interval determinat, proveu aquesta fórmula:
=COMPTARSI(A1:B12,"")
Comptar cel·les en blanc
Si voleu comptar cel·les en blanc en un interval determinat, utilitzeu la funció COUNTIF amb * caràcter comodí i operador a l'argument criteri per comptar cel·les buides.
Aquesta fórmula compta les cel·les que no contenen cap valor de text:
=COMPTARSI(A1:B12,""&"*")Des de * El comodí coincideix amb qualsevol valor de text, la fórmula anterior comptarà totes les cel·les que no siguin iguals a *. També compta les cel·les amb dates i números com a espais en blanc.
Per comptar tots els espais en blanc (tots els tipus de valors):
=COMPTARSI(A1:B12,"")Aquesta funció compta només les cel·les buides de l'interval.
Utilitzant la funció COUNTIF per comptar les dates
Podeu comptar cel·les amb dates (igual que heu fet amb els criteris de nombre) que compleixin una condició lògica o la data o data especificada a la cel·la de referència.
Per comptar les cel·les que contenen la data especificada (05-05-2020), utilitzaríem aquesta fórmula:
=COMPTARSI(B2:B10,"05-05-2020")
També podeu especificar una data en diferents formats com els criteris de la funció COUNTIF com es mostra a continuació:
Si voleu comptar cel·les que contenen dates anteriors o posteriors a una data determinada, utilitzeu els operadors menor que (abans) o major que (després) juntament amb la data específica o la referència de cel·la.
=COMPTARSI(B2:B10,">=05/05/2020")
També podeu utilitzar una referència de cel·la que contingui una data combinant-la amb l'operador (entre cometes dobles).
Per comptar el nombre de cel·les de l'interval A2:A14 amb una data anterior a la data a E3, utilitzeu la fórmula següent, on l'operador més gran que (<) significa abans de la data a E3.
=COMPTARSI(A2:A14,"<"&E3)
Alguns exemples de fórmules i el seu resultat:
Data de recompte en funció de la data actual
Podeu combinar la funció COUNTIF amb les funcions de data específiques d'Excel, és a dir, TODAY () per comptar les cel·les que tenen la data actual.
=COMPTARSI(A2:A14,">"&AVUI())Aquesta funció compta totes les dates des d'avui en el rang (A2:A14).
Comptar les dates entre un interval de dates específic
Si voleu comptar totes les dates entre dues dates, heu d'utilitzar dos criteris a la fórmula.
Ho podem fer utilitzant dos mètodes: funcions COUNTIF i COUNTIFS.
Utilitzant la funció COUNTIF d'Excel
Heu d'utilitzar dues funcions COUNTIF per comptar totes les dates entre les dues dates especificades.
Per comptar les dates entre el ’09-02-2020′ i el ’20-08-2021′, utilitzeu aquesta fórmula:
=COMPTARSI(A2:A14,">09-02-2020")-COMPTARSI(A2:A14,">20-08-2021")Aquesta fórmula cerca primer el nombre de cel·les que tenen una data posterior al 2 de febrer i resta el recompte de cel·les amb dates posteriors al 20 d'agost. Ara obtenim el núm. de cel·les que tenen dates posteriors al 2 de febrer i abans del 20 d'agost (el nombre és 9).
Si no voleu que la fórmula compti tant el 2 de febrer com el 20 d'agost, feu servir aquesta fórmula:
=COMPTARSI(A2:A14,">09-02-2020")-COMPTARSI(A2:A14,">=20-08-2021")Només heu de substituir l'operador ">" per ">=" al segon criteri.
Utilitzant la funció COUNTIFS d'Excel
La funció COUNTIFS també admet diversos criteris i, a diferència de la funció COUNTIF, compta les cel·les només després que es compleixin totes les condicions. Si voleu comptar cel·les amb totes les dates entre dues dates especificades, introduïu aquesta fórmula:
=COMPTARSI(A2:A14,">"&A11;A2:A14,"<"&A10)
Si voleu incloure també les dates especificades al recompte, utilitzeu els operadors ">=" i "<=". Aquí, aneu amb aquesta fórmula:
=COMPTARSI(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")Hem utilitzat la data directament als criteris en lloc de la referència de cel·la per a aquest exemple.
Com gestionar COUNTIF i COUNTIFS amb diversos criteris a Excel
La funció COUNTIF s'utilitza principalment per comptar cel·les amb un sol criteri (condició) en un rang. Però encara podeu utilitzar COUNTIF per comptar cel·les que coincideixen amb diverses condicions del mateix interval. Tanmateix, la funció COUNTIFS es pot utilitzar per comptar cel·les que compleixen diverses condicions en els mateixos intervals o diferents.
Com comptar els números dins d'un rang
Podeu comptar cel·les que contenen números entre els dos nombres especificats mitjançant dues funcions: COUNTIF i COUNTIFS.
COUNTIF per comptar números entre dos nombres
Un dels usos habituals de la funció COUNTIF amb múltiples criteris és comptar els nombres entre dos nombres especificats, p. per comptar nombres superiors a 10 però inferiors a 50. Per comptar números dins d'un interval, uniu dues o més funcions COUNTIF juntes en una fórmula. Deixa'ns mostrar-te com.
Suposem que voleu comptar cel·les de l'interval B2:B9 on un valor és superior a 10 i inferior a 21 (sense incloure 10 i 21), aneu amb aquesta fórmula:
=COMPTARSI(B2:B14,">10")-COMPTARSI(B2:B14,">=21")La diferència entre dos nombres es troba restant una fórmula d'una altra. La primera fórmula compta els nombres més grans que 10 (que és 7), la segona fórmula retorna el recompte de nombres superiors o iguals a 21 (que és 4) i el resultat de la segona fórmula es resta de la primera fórmula (7 -4) per obtenir el recompte de nombres entre dos nombres (3).
Si voleu comptar cel·les amb un nombre superior a 10 i inferior a 21 a l'interval B2:B14, inclosos els números 10 i 21, utilitzeu aquesta fórmula:
=COMPTARSI(B2:B14,">=10")-COMPTARSI(B2:B14,">21")
COUNTIFS per comptar números entre 2 nombres
Per comptar els nombres entre 10 i 21 (excepte el 10 i el 21) que es troben a les cel·les B2 a B9, utilitzeu aquesta fórmula:
=COMPTARSI(B2:B14,">10", B2:B14,"<21")
Per incloure 10 i 21 al recompte, només cal que utilitzeu "més gran o igual a" (>=) en comptes de "més gran que" i "menys o igual a" (<=) en comptes dels operadors "menys que" a les fórmules. .
COUNTIFS per comptar cel·les amb criteris múltiples (criteri AND)
La funció COUNTIFS és la contrapartida plural de la funció COUNTIF que compta les cel·les en funció de dos o més criteris en el mateix o en diversos intervals. Es coneix com a "lògica AND" perquè la funció es fa per comptar cel·les només quan totes les condicions donades són VERTADES.
Per exemple, volem saber quantes vegades (recompte de cel·les) s'ha venut aquest pa (valor a la columna A) per menys de 5 (valor a la columna C).
Podem utilitzar aquesta fórmula:
=COMPTARSI(A2:A14,"Pa", C2:C14,"<5")
COUNTIF per comptar cel·les amb criteris múltiples (criteris O)
Si voleu comptar el nombre de cel·les que compleixen diversos criteris en el mateix interval, uniu dues o més funcions COUNTIF. Per exemple, si voleu esbrinar quantes vegades es repeteixen "Pa" o "Formatge" en l'interval especificat (A2:A14), utilitzeu la fórmula següent:
=COMPTARSI(A2:A14,"Pa")+COMPTARSI(A2:A14,"Formatge")Aquesta fórmula compta les cel·les per a les quals almenys una de les condicions és TRUE. Per això s'anomena 'OR lògica'.
Si voleu avaluar més d'un criteri en cadascuna de les funcions, és millor utilitzar COUNTIFS en lloc de COUNTIF. A l'exemple següent, volem obtenir el recompte de l'estat "Encarregat" i "Enviat" per a "Pa", de manera que utilitzaríem aquesta fórmula:
=COMPTARSI(A2:A14,"Pa", C2:C14, "Encarregat")+COMPTARSI(A2:A14,"Pa", C2:C14, "Entrada")
Esperem que sigui fàcil, però més aviat un tutorial llarg us donarà una idea sobre com utilitzar les funcions COUNTIF i COUNTIF a Excel.