La función COUNTIFS de Excel se utiliza para obtener el número de celdas de uno o más rangos que cumplen una serie de condiciones o criterios especificados.
¡Ocasionalmente, la función COUNTIFS no funciona como se esperaba y devuelve un #VALOR ! 0 , un mensaje que indica que hay un error en la fórmula o algún otro valor inesperado.
Estas son algunas de las razones por las que la función no funciona como se espera en Microsoft Excel :
- Cuando una fórmula hace referencia a un rango de un libro que está cerrado.
- Utilizar un valor de otra celda sin concatenar con el operador lógico utilizado.
- No encerrar los criterios de texto entre comillas (» «).
- Los rangos posteriores no tienen el mismo número de columnas y filas que el primer rango de criterios.
- Los criterios numéricos y el operador lógico no van entre comillas (» «).
- Intenta obtener un número utilizando la lógica OR.
- Cambios posteriores en el conjunto de datos.
- Errores en la fórmula.
En este tutorial explicamos cómo se pueden resolver estos motivos para que la función devuelva el número correcto que buscamos.
1. La fórmula hace referencia a un rango en otro libro de trabajo que está cerrado.
Cuando la función COUNTIFS hace referencia a un rango en otro libro de trabajo cerrado, devuelve un error #¡VALOR! en lugar del número de celdas esperado.
En el siguiente ejemplo, en lugar de que la fórmula devuelva el número de productos rojos, se generó un error #¡VALOR! porque la fórmula hace referencia a un rango en otro libro de trabajo cerrado.su libro de trabajo llamado Colores de productos que está cerrado.
Solución
La solución para que las cuentas no funcionen en este escenario es abrir el libro cerrado y pulsar la tecla F9 del teclado para que la fórmula recalcule los parámetros correctos. Así obtendremos la cuenta correcta, como se muestra en el siguiente ejemplo.
2. Utilizar el valor de otra celda sin concatenar la referencia de celda al operador lógico
Si utiliza un operador lógico y una referencia de celda como criterios para la función COUNTIFS en una fórmula, y no concatena la referencia de celda al operador lógico insertando & antes de la referencia de celda, la función devolverá un 0 en lugar del número esperado.
Esto se ilustra en el siguiente ejemplo en el que se ha utilizado la fórmula =COUNTIFS(G2:G6,»>M2″).
Solución
En el siguiente ejemplo, tenemos que introducir la fórmula correcta =COUNTIFS(G2:G6,»> » &M2) en la celda I3. Cuando pulsamos ENTER , obtenemos el número correcto de 4.
En la fórmula correcta, sólo el operador lógico mayor que (>) va entre comillas (» «), y el operador de concatenación & se coloca antes de la referencia de celda M2.
3. No ponga comillas ( » «) entre los criterios de texto
Si los criterios de texto no se encierran entre comillas, la función COUNTIFS devolverá un valor de 0 en lugar del número correcto que esperamos, como se muestra en el ejemplo siguiente.
La fórmula en la celda J2 es =COUNTIFS(C2:C6,Rojo). La fórmula devuelve 0 porque el criterio Rojo no estaba entre comillas (» «).
Solución al problema de las cuentas que no funcionan
Tenemos que encerrar entre comillas los criterios de texto de la fórmula, como se muestra en el siguiente ejemplo, y entonces obtendremos el número correcto de 2.
4. El siguiente conjunto de criterios no tiene la misma forma que el primer conjunto de criterios.
Cuando utilizamos varios criterios en la función COUNTIFS y los rangos de criterios opcionales adicionales no corresponden al primer rango de criterios, se genera el error #VALOR.
En el siguiente ejemplo, se ha introducido la fórmula =COUNTIFS(B2:B13, «Rojo»,C2:C10, «Microsoft») en la celda G3 para intentar obtener el número de productos rojos suministrados por Microsoft.
Cuando pulsamos Intro, obtenemos el error #¡VALOR! como se muestra a continuación.
Esto se debe a que el segundo rango de criterios C2:C10 no tiene el mismo número de celdas que el primer rango de criterios B2:B13.
Solución
Para corregir este problema, debemos asegurarnos de que el segundo rango de criterios tenga el mismo tamaño que el primer rango de criterios, como se muestra a continuación:
Cuando pulsamos INTRO, obtenemos el número correcto de 2, como se muestra a continuación:
5. No encierre los criterios numéricos y el operador lógico entre comillas (» «)
Cuando utilizamos operadores lógicos como igual a (=), mayor que (>), menor que (<) y no igual a (<>) en una fórmula y no insertamos el criterio numérico y el operador lógico entre las mismas comillas, Excel nos envía un mensaje indicando que la fórmula contiene un error.
En el siguiente ejemplo, no hemos colocado el operador lógico y el criterio numérico entre comillas. Sólo el operador lógico está entre comillas.
Cuando pulsamos INTRO, obtenemos el siguiente mensaje:
Solución
Debemos asegurarnos de incluir tanto el operador lógico como el criterio numérico dentro de las mismas comillas, como se muestra a continuación:
Cuando pulsamos ENTER, obtenemos el número correcto de 5, como se muestra a continuación:
6. Intentando obtener un recuento utilizando la lógica OR
Aunque la función COUNTIFS puede generar conteos correctos utilizando la lógica AND, sólo genera un valor 0 cuando intentamos utilizarla para calcular utilizando la lógica OR.
En el siguiente ejemplo, estamos intentando obtener el número de categorías de productos rojos y azules utilizando la lógica OR.
Cuando presionamos ENTER, obtenemos el valor inesperado de 0, como se muestra a continuación. Esto se debe a que la función COUNTIFS no puede calcular utilizando la lógica OR.
Solución
Podemos evitar este problema de lógica OR utilizando las funciones COUNTIFS y SUM juntas.
En el siguiente ejemplo, hemos introducido la fórmula =SUMA(COUNTIFS(B2:B13,{«Rojo», «Azul»})) en la celda G3.
Cuando pulsamos INTRO, obtenemos el valor correcto de 6, como se muestra a continuación. Esto se debe a que la función COUNTIFS devolvió 3 números de rojos y 3 números de azules del rango de criterios B2:B13, y la función SUM sumó los valores para obtener 6.
7. Cambios posteriores en el conjunto de datos
Los cambios en un conjunto de datos pueden hacer que la función COUNTIFS no genere los valores esperados. Por ejemplo, en el siguiente conjunto de datos, la función COUNTIF funciona correctamente y devuelve el número correcto de productos azules.
Pero si hay cambios en los datos, por ejemplo en este caso añadimos la palabra color a Azul, la función COUNTIFS muestra ahora el valor incorrecto de 0.
Solución
Esta situación puede remediarse utilizando comodines con la función COUNTIF.
En el siguiente ejemplo, hemos utilizado el carácter asterisco (*) en los criterios, lo que nos permite obtener el número correcto de 4 productos. La función COUNTIF puede ahora buscar coincidencias parciales en el rango de criterios.
8. Errores en la fórmula
A veces la función COUNTIF no funciona debido a un error en la fórmula, por ejemplo el olvido de una coma de separación entre el rango de criterios y el operador lógico.
En el siguiente ejemplo, no hay ninguna coma entre el rango de criterios D2:D13 y el operador lógico mayor que (>).
Al pulsar INTRO, aparece un cuadro de mensaje informándonos de que hay un error en la fórmula.
Solución
Hay que tener especial cuidado al escribir fórmulas para asegurarse de que la sintaxis es correcta. En el siguiente ejemplo, insertando una coma entre el rango de criterios y el operador mayor que (>) se soluciona el problema y obtenemos el número correcto de 5.
Conclusión
COUNTIFS es una función muy potente que se puede utilizar para generar el número de celdas en rangos de datos que cumplen condiciones específicas.
En este tutorial, hemos visto las distintas razones por las que esta función no siempre funciona como se espera y las formas de resolverlas.