Contar el número de celdas que no contienen varios valores

Normalmente, es sencillo contar celdas que no contienen un valor específico mediante la función CONTAR.SI. Esta entrada explica paso a paso cómo contar el número de celdas que no incluyen varios valores dentro de un rango limitado en Excel.

¿Cómo contar celdas que no contienen ninguno de varios valores especificados?

Como se muestra en la siguiente captura de pantalla, para contar las celdas del rango B3:B11 que no contienen los valores especificados en D3:D4, puede seguir estos pasos.

Fórmula genérica

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

Argumentos

Nota: Esta fórmula debe introducirse como una fórmula matricial. Si, tras aplicarla, aparecen llaves alrededor de la fórmula, significa que se ha creado correctamente como fórmula matricial.

¿Cómo utilizar esta fórmula?

1. Seleccione una celda vacía para mostrar el resultado.

2. Introduzca la siguiente fórmula y, a continuación, pulse simultáneamente las teclas Ctrl+Mayús+Intro para obtener el resultado.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

¿Cómo funcionan estas fórmulas?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4): La función TRANSPONER cambia la orientación de D3:D4 y devuelve {“count”,”blank”};
• SEARCH({“count”,”blank”},B3:B11): La función HALLAR busca en el rango B3:B11 la posición de las subcadenas «count» y «blank» y devuelve una matriz como {#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;1;#¡VALOR!;#¡VALOR!;8;1;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;1;
  #¡VALOR!;1,7}.
• En este caso, cada celda del rango B3:B11 se buscará dos veces, ya que hay dos valores que desea excluir al contar celdas, lo que dará como resultado 18 valores en la matriz. Cada número de dicha matriz indica la posición del primer carácter de «count» o «blank» en cada celda del rango B3:B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}: La función ESNUMERO devuelve VERDADERO si encuentra números en la matriz y FALSO si encuentra errores. Aquí devuelve el resultado como {FALSO;FALSO;FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO;FALSO;FALSO;FALSO;FALSO;VERDADERO;FALSO;
  VERDADERO;VERDADERO}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,)
  FALSE;TRUE,TRUE}): Estos dos signos menos convierten “VERDADERO” en 1 y “FALSO” en 0. Aquí obtendrá una nueva matriz como {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0: La función FILA devuelve el número de fila de cada celda en la referencia: {3;4}. A continuación, el operador de exponenciación (^) eleva los números 3 y 4 a la potencia 0, lo que da como resultado {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): La función MMULT devuelve el producto matricial de estas dos matrices: {0;0;1;1;1;0;0;1;2}, adaptándose así a los datos originales. Cualquier valor distinto de cero en la matriz indica que se ha encontrado al menos una de las cadenas excluidas, mientras que un cero significa que no se ha detectado ninguna cadena excluida.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: Aquí se evalúa si cada número de la matriz es mayor que 0. Si lo es, devuelve VERDADERO; si no, devuelve FALSO. Así obtendrás una nueva matriz como {FALSO;FALSO;VERDADERO;VERDADERO;VERDADERO;FALSO;FALSO;VERDADERO;VERDADERO}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Como solo queremos contar las celdas que no contienen los valores especificados, debemos invertir estos valores en la matriz restándolos de 1. El operador matemático convierte automáticamente los valores VERDADERO y FALSO en 1 y 0, respectivamente, y devuelve como resultado {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}La función SUMA suma todos los números de la matriz y devuelve un resultado final de 4.

Funciones relacionadas

Fórmulas relacionadas