Contar el número de celdas distintas de varios valores en Excel

En Excel, puede obtener fácilmente el número de celdas distintas de un valor específico mediante la función CONTAR.SI, pero ¿alguna vez ha intentado contar las celdas que no coinciden con varios valores? Por ejemplo, deseo obtener el número total de productos en la columna A excluyendo los artículos especificados en el rango C4:C6, tal como se muestra en la siguiente captura de pantalla. En este artículo, presentaré algunas fórmulas para resolver esta tarea en Excel.

• Contar el número de celdas distintas de varios valores con la fórmula 1
• Contar el número de celdas distintas de varios valores con la fórmula 2

Contar el número de celdas distintas de varios valores con la fórmula 1

Para contar celdas distintas de varios valores especificados, puede combinar las funciones COINCIDIR, ESNOD y SUMAPRODUCTO. La sintaxis genérica es:

• range: La lista de celdas que desea contar;
• exclude_values: Los valores que deben excluirse de la lista de rango.

1. Introduzca o copie la siguiente fórmula en una celda vacía para mostrar el resultado:

2. A continuación, pulse la tecla Entrar para obtener el resultado correcto. Consulte la captura de pantalla:

Explicación de la fórmula:

• COINCIDIR(A2:A13;C2:C4;0): Esta función COINCIDIR devuelve la posición del primer valor coincidente como un número y el error #N/D cuando no encuentra ninguna coincidencia. Es decir, para cada valor en A2:A13, si coincide con alguno de los valores en C2:C4, devuelve su posición relativa dentro de C2:C4; de lo contrario, devuelve #N/D. El resultado es: {1;#N/D;#N/D;1;3;2;#N/D;#N/D;3;#N/D;#N/D;#N/D}.
• ESNOD(COINCIDIR(A2:A13;C2:C4;0))= ESNOD({1;#N/D;#N/D;1;3;2;#N/D; #N/D;3;#N/D; #N/D; #N/D}): La función ESNOD transforma los valores de error en VERDADERO y los números en FALSO, obteniendo así: {FALSO;VERDADERO;VERDADERO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO;VERDADERO;VERDADERO;VERDADERO}.
• --(ESNOD(COINCIDIR(A2:A13;C2:C4;0)))=--({FALSO;VERDADERO;VERDADERO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO;VERDADERO;VERDADERO;VERDADERO}): El operador doble negativo (--) transforma todos los VERDADERO en 1 y los FALSO en 0, por lo que: SUMAPRODUCTO(--(ESNOD(COINCIDIR(A2:A13;C2:C4;0)))) = SUMAPRODUCTO({0;1;1;0;0;0;1;1;0;1;1;1}).
• A continuación, la función SUMAPRODUCTO suma los números del array y obtiene el resultado: 7.

Contar el número de celdas distintas de varios valores con la fórmula 2

Para resolver esta tarea en Excel, también puede utilizar la combinación de las funciones CONTARA, SUMAPRODUCTO y CONTAR.SI. La sintaxis genérica es:

• range: La lista de celdas que desea contar;
• exclude_values: Los valores que se deben ignorar de la lista de rango.

1. Introduzca o copie la siguiente fórmula en una celda vacía para obtener el resultado:

2. A continuación, pulse la tecla Entrar para obtener el resultado que se muestra en la siguiente captura de pantalla:

Explicación de la fórmula:

• CONTARA(A2:A13): Esta función CONTARA devuelve el número de celdas no vacías en el rango A2:A13. El resultado obtenido es: 12.
• CONTAR.SI(A2:A13;C2:C4): La función CONTAR.SI cuenta las celdas del rango A2:A13 que coinciden con los criterios especificados en C2:C4, obteniendo como resultado {2;1;2}, lo que indica que «Manzana» aparece dos veces, «Uva» una vez y «Limón» dos veces.
• SUMAPRODUCTO(CONTAR.SI(A2:A13;C2:C4))=SUMAPRODUCTO({2;1;2}): Esta función SUMAPRODUCTO suma todos los elementos del array y devuelve un resultado final: 5.
• CONTARA(A2:A13)-SUMAPRODUCTO(CONTAR.SI(A2:A13;C2:C4))=12-5: El resultado (5) de la función SUMAPRODUCTO se resta del recuento total de celdas no vacías (12) para obtener el resultado final que necesita.

Nota: Ninguna de las fórmulas anteriores es Distinguir Mayúsculas y Minúsculas.

Función relacionada utilizada:

Más artículos: