CONTAR.SI en Excel: contar valores cuando se cumple una condición

Hay muchas funciones en Excel pero dos de las más útiles y poderosas son CONTAR y SI.

La función CONTAR y sus diferentes variantes son extremadamente útiles porque realizan un recuento de valores, tanto es así que en la barra inferior de la ventana de Microsoft Excel, siempre que seleccionamos un conjunto de valores, nos aparece automáticamente el resultado de la función CONTARA (variante de CONTAR que cuenta valores numéricos o no) bajo el nombre “Recuento” junto al promedio y la suma:

En cuanto a la función SI, como hemos dicho en otras ocasiones, se trata de una de las funciones con más poder y posibilidades de Microsoft Excel pues permite que SI se cumple una condición, se realice un cálculo o se ejecute alguna otra función, de manera que con el uso de la función SI convertimos una hoja de Excel en un pequeño sistema de programación en el que si sucede un evento, se desencadena una acción (la base de la programación informática).

Pues con la parte más funcional de cada una de estas fórmulas, Excel nos pone a disposición la variante CONTAR.SI en la que se realiza un recuento de valores (numéricos o no) según si se cumple o no una condición que le podemos especificar como argumento, es decir, podemos contar los valores que cumplen un requisito indicado por nosotros mismos.

Aplicaciones de la función

Estrictamente, la función CONTAR.SI sirve para realizar un recuento de las celdas que incluyan un valor que cumpla una condición especificada en la función. Dicho así puede no parecer demasiado versátil pero nada más lejos de la realidad.

La utilidad de esta función es muy amplia aunque lo más habitual es usarla para ver cuántas veces se repite un valor concreto en un listado de muchos registros, por ejemplo, cuántos de mis clientes tienen un saldo superior a un valor, cuántos de mis productos tienen un stock inferior a cierta cantidad, cuántas veces se repite un mismo nombre dentro de un listado o cuántas veces he leído un mismo código de barras.

Sintaxis de CONTAR.SI

La sintaxis es muy sencilla en cuanto a cantidad de argumentos, puesto que solo tenemos que especificar dos, pero la verdad es que el segundo argumento, el del criterio, no es tan fácil de acertar. Vamos a verlos en detalle:

Rango

Como siempre que hablamos de rango en Excel, nos referimos a un conjunto de celdas, especificando desde la primera a la última celda, en el que se encuentran los valores sobre los que queremos actuar. En el caso de CONTAR.SI el rango hace referencia al conjunto de celdas en el que se encuentran los valores que queremos contar una vez que se haya cumplido la condición que especificaremos en el argumento “criterio”. Seguro que no es el argumento “rango” el que te ha traído hasta este tutorial 😉

Criterio

El criterio en CONTAR.SI hace referencia a la condición que queremos que se cumpla para que los valores sean contados o no.

Para que se pueda entender bien y cada usuario pueda centrarse en el caso que le preocupa o con el que quiere usar esta función, vamos a ver en detalle cada una de las posibilidades que ofrece esta función en cuanto a especificar un criterio:

Igual a un número

Uno de los criterios más sencillos que vamos a poder utilizar para especificar en la función CONTAR.SI es el de contar los valores que sean iguales a un cierto valor numérico, por ejemplo, contar todas aquellas celdas en el rango desde la celda A1 hasta la celda B10 que sean iguales a 8:

=CONTAR.SI(A1:B10;8)

Basta con indicar un número 8 como argumento “criterio” de la función. No es necesario indicar el signo “=” ni entrecomillar el número. Entrecomillamos en caso de que se trate de un texto pero no es necesario para un valor numérico.

Tenemos un listado de más de 1000 DVDs de una biblioteca. Vamos a aplicar los cálculos sobre esta hoja de ejemplo para que se entiendan mejor:

Dado el listado de DVDs con el número de discos de cada ejemplar, podemos calcular como ejemplo de la aplicación de CONTAR.SI al recuento de un número ¿Cuántos de estos DVDs contienen 8 discos?

La solución es:

=CONTAR.SI(D:D;8)

Y el resultado es 12, que son las veces que se repite el número 8 en la columna D completa (D:D)

Igual a un texto

De la misma forma que podemos hacer un recuento de aquellas celdas con un valor numérico concreto, podemos hacerlo también de un texto. El único detalle a tener en cuenta es el de incluir el texto entre comillas.

Viéndolo en el ejemplo: ¿Cuántos de los DVDs tienen como autor a Disney, Walt?

La solución es:

=CONTAR.SI(C:C;»Disney, Walt»)

Y el resultado es 10, que son las veces que se encuentra exactamente el texto “Disney, Walt” en la columna C. El apellido y el nombre están separados por una coma y un espacio, cualquier diferencia en estos caracteres ya no se tendría en cuenta como resultado.

Igual al valor de una celda

En los ejemplos anteriores, hemos especificado el argumento con el criterio pero podemos indicar como criterio a cumplir que el valor sea igual a la referencia a una celda, por ejemplo, según nos indica la celda E8, vemos que uno de los DVDs de ¿Conoces a Joe Black? Se encuentra en el departamento de inglés, no en la biblioteca. ¿Cuántos de los DVDs del listado se encuentran en el departamento de inglés?

Podríamos resolverlo utilizando CONTAR.SI y especificar en el argumento criterio “Dept. de Inglés” pero fijémonos en que contiene un par de mayúsculas y una abreviatura, no sería difícil escribirlo de forma inexacta y que la función no nos devolviera el resultado esperado.

Vamos a hacerlo de forma más sencilla indicando como argumento “criterio” simplemente la celda E8:

La solución por tanto sería:

=CONTAR.SI(E:E;E8)

Y el resultado 69, que no sería el número de discos porque un título puede tener más de un disco pero sí el número de títulos que se encuentran en el departamento de inglés.

Podríamos inmovilizar la celda E8 en caso de querer arrastrar esta fórmula para que la celda a la que hace referencia el criterio no se mueva en el arrastre. Esto se hace con F4 y la función quedaría:

=CONTAR.SI(E:E;$E$8)

Completamente válido, con el mismo resultado y apta para realizar un arrastre sin mover la referencia a la celda E8.

Igual al resultado de una función

Aunque exista un apartado dedicado a las funciones anidadas, vamos  a ver un sencillo ejemplo de aplicación de funciones anidadas en Excel y vamos a hacerlo como argumento de la función CONTAR.SI:

Podemos anidar (meter una dentro de otra) las funciones en Excel teniendo en cuenta que el cálculo se realiza desde la función más interior hacia la más exterior. Como “interior” Excel comprende aquella función cuyo paréntesis se ha abierto en último lugar y cerrado en primer lugar.

Son los paréntesis los que nos tienen que servir de referencia al anidar funciones:

=FUNCIÓN_EXTERIOR(FUNCIÓN_INTERIOR(“Porque este paréntesis está en el interior del otro”))

En este ejemplo vamos a calcular cuántos de los títulos que contiene el listado tienen el número máximo de discos (dato que no conocemos inicialmente), para eso utilizamos la función MAX sobre la columna D que nos indicará el número máximo de discos en un mismo DVD y anidaremos esa función en CONTAR.SI como argumento criterio para que esta nos cuente solamente los DVDs que obtengan ese resultado:

La solución en este caso es:

=CONTAR.SI(D:D;MAX(D:D))

Donde el resultado del valor máximo es 30 (el título que más discos tiene es “Enciclopedia de la astronomía y el espacio” con 30 discos) y el resultado de la función de recuento es 1, puesto que no existen más títulos con 30 discos.

Menor que un número

Para que la función haga un recuento solo de aquellos valores menores a una cierta cantidad, podemos utilizar como parte del argumento criterio en signo “menor que” (<) con la peculiaridad de tenerlo que incluir entrecomillado junto al valor numérico que queremos especificar como menor, por ejemplo, para indicar “menor que 5” debemos escribir “<5” como argumento.

Usando

=CONTAR.SI(D:D;»<5″)

Tenemos como resultado que 1002 títulos tienen menos de 5 discos.

Menor o igual que un número

Como variación del apartado inmediatamente anterior, podemos especificar que el recuento se haga sobre los valores menores o iguales a una cierta cantidad; para hacerlo, tenemos que añadir el signo igual (=) a continuación del signo menor que (<), de esta forma, para contar los valores menores o iguales a 5, indicamos igualmente entrecomillado “<=5”

=CONTAR.SI(D:D;»<=5″)

Nos resulta en 1004 títulos con hasta 5 discos (con lo que solo 2 títulos tienen exactamente 5 discos).

Menor o menor o igual que el valor de una celda

Seguramente en este punto ya has asumido la lógica que utiliza la función CONTAR.SI en cuanto al argumento criterio: entrecomillamos textos y signos de comparación pero no referencias a celdas ni funciones, de manera que para indicar que el recuento se haga sobre aquellos valores menores a los de una celda concreta (por ejemplo la celda D10 coloreada en verde) utilizamos:

=CONTAR.SI(D:D;»<«&D10)

Uniendo el signo menor que y la referencia a la celda D10 con el signo &.

Menor o menor o igual que el resultado de una función

De la misma forma que en el ejemplo anterior, unimos el signo de menor que con la función a calcular mediante el signo et (&), teniendo en cuenta que el resultado de la función será el valor respecto al que tendrá que ser menor el resultado para formar parte del recuento, por lo tanto, si queremos recontar todos aquellos títulos que tengan menos discos que el promedio de toda la columna D (en la que están todos los números de discos de cada título), la función a expresar sería:

=CONTAR.SI(D:D;»<«&PROMEDIO(D:D))

Y tenemos como resultado 979, que son los DVDs con un único disco y por tanto, menos de 1,18.

Todo lo visto con menor que, pero con mayor que

Hemos visto los diferentes ejemplos hasta ahora utilizando las comparaciones “igual que” y “menor que” pero por supuesto, podemos utilizar otros comparadores como por ejemplo “mayor que” para contar solo aquellos valores mayores que un criterio. En el anterior ejemplo hemos visto cómo recontar aquellos DVDs que tienen menos discos que el promedio (que es de 1,18 discos por título) y cambiando el comparador, podemos calcular los que tienen más discos que el promedio, por lo tanto, el número de discos es mayor que la función PROMEDIO:

=CONTAR.SI(D:D;»>»&PROMEDIO(D:D))

Y evidentemente solo hay que cambiar el operador entrecomillado por mayor que (>).

Diferente de

Por supuesto, y por ser un operador muy importante en las funciones de Excel, hagamos el ejemplo también para el recuento de aquellos valores diferentes de un valor dado.

El operador a utilizar en este caso se compone de la unión de menor que y mayor que: <>

Por ejemplo, para hacer un recuento de cuántas celdas diferentes de vacías hay en la columna B, indicamos:

=CONTAR.SI(B:B;»<>»&»»)

Y obtenemos el resultado 1021 que corresponde a los 1019 DVDs más los dos títulos de columna que hay al inicio de la columna B.

(Aunque habría una función específica para hacer esto, la función CONTARA() que hace un recuento de celdas no vacías)

Contiene

Para contar solo aquellas celdas que contienen un cierto texto, no nos queda más remedio que utilizar los comodines. El asterisco (*) sustituye a cualquier cadena de caracteres mientras que el cierre de interrogación (?) sustituye un único carácter.

Si queremos hacer un recuento de todos aquellos DVDs que tengan como director a Amenábar e independientemente de que el apellido se haya escrito con el acento correspondiente en la a o no, podemos hacer:

=CONTAR.SI(C:C;»Amen?bar*»)

De manera que se contarán todas las celdas de la columna C que contengan «Amenabar» o «Amenábar» seguidos de cualquier texto.

Resumen de criterios

Veamos un resumen en forma de tabla de los diferentes tipos de criterios vistos:

– RESUMEN DE CRITERIOS –
Igual a un número
Sin comillas y sin =
Igual a un texto
Entre comillas
Igual al valor de una celda
Referencia a la celda, sin comillas
Igual al resultado de una función
Anidar la función como argumento, sin comillas y sin =
Menor que un número
Símbolo menor < y número, juntos y entrecomillados “<5”
Menor o igual que un número
Símbolo menor e igual <= y  número, juntos y entrecomillados “<=5”
Menor o menor o igual que el valor de una celda
Símbolo menor e igual <= entrecomillados y  la referencia a la celda (sin comillas), unidos por el signo &: “<=”&A1
Menor o menor o igual que el resultado de una función
Símbolo menor e igual <= entrecomillados y  la función (sin comillas), unidos por el signo &: “<=”&PROMEDIO(A:A)
Mayor que un valor
Igual que los ejemplos anteriores pero con el signo “>” entrecomillado
Diferente de
Menor que y mayor que entrecomillados “<>” siempre seguidos del valor a comparar
Contiene
Utilizamos los comodines ? o * para sustituir uno o varios caracteres

Errores más habituales

Los errores más habituales en la función CONTAR.SI se suelen dar al expresar de forma incorrecta el argumento criterio y el error más frecuente en este caso es el de no usar correctamente las comillas, por eso hacemos tanto hincapié en este aspecto durante todo el post.

Además del error de las comillas, pueden darse otros casos que acaben ofreciendo cálculos erróneos, por ejemplo:

Textos con comillas

Tenemos que vigilar que el contenido del texto que indicamos como argumento no tenga comillas en su contenido, pues esto alterará la forma en que delimitamos y entrecomillamos el propio argumento y provocará un resultado no satisfactorio.

Mayúsculas y minúsculas

La función CONTAR.SI no distingue entre mayúsculas y minúsculas, de manera que nos puede dar un valor superior al esperado. Aunque especifiquemos como criterio una palabra con mayúsculas, se hará el recuento de los textos que coincidan aun estando en minúscula.

=CONTAR.SI(A:A;”TEXTO”) nos ofrecerá el mismo resultado que =CONTAR.SI(A:A;”texto”) o =CONTAR.SI(A:A;”TextO”)

Cadenas de texto muy largas

La función tiene en cuenta textos de hasta 255 caracteres, pero no más. Si la queremos usar para hacer un recuento de textos demasiado largos, el resultado no será el esperado. Siempre podemos dividir los textos largos en varias partes y unirlas posteriormente o concatenar varios trozos de texto inferiores a 255 caracteres.

CONTAR.SI.CONJUNTO: el más difícil todavía

Podemos usar la función CONTAR.SI.CONJUNTO para aplicar más de un criterio sobre más de un rango y complicar un poco más la función CONTAR.SI. Sin duda, CONTAR.SI.CONJUNTO es una variante muy interesante de CONTAR.SI y por eso mismo, le dedicaremos su propio espacio.