FÓRMULAS DE EXCEL

Contar filas con condiciones múltiples negativas en Excel

Contar filas con condiciones múltiples negativas en Excel

Funciones utilizadas: SUMAPRODUCTO, ESNOD, COINCIDIR

Suponiendo que disponemos de una lista con encabezados en el rango A1:C15 con distintas notas de examenes por alumno, donde el rango A2:A15 contiene los nombres de los alumnos, el rango B2:B15 contiene las notas del examen 1 para cada alumno y el rango C2:c15 las notas del examen 2 para cada alumno, y que necesitamos obtener en las celda E9 la cantidad de filas del rango A2:C15 que cumplen dos condiciones específicas, de las cuales una es una condición de igualdad y otra es una condición excluyente o negativa. Dichas condiciones van a estar definidas por la celda F3 (igualdad) y el rango F4:F5 (valores excluyentes). La sintaxis que debemos utilizar para obtener la cantidad de filas que cumplen con las dos condiciones mencionadas es la siguiente:

=SUMAPRODUCTO((B2:B15=F3)*ESNOD(COINCIDIR(C2:C15;F4:F5;0)))

Donde B2:B15 corresponde al rango de valores con las notas del examen 1, F3 corresponde al valor que debemos igualar por cada valor del rango B2:B15, C2:C15 corresponde al rango de valores con las notas del examen 2, y F4:F5 corresponde al rango de valores que no deben ser iguales a cada valor del rango en C2:C15.

La primer ecuación de la fórmula pone a prueba cada valor de la columna B con el valor de la celda F3 devolviendo una matriz de resultados VERDADERO y FALSO, donde los valores VERDADERO corresponden a aquellos valores iguales a F3 (nota 7 del primer examen). La segunda ecuación pone a prueba cada valor de la columna C con el rango de valores en F4:F5 pero utilizando la combinación de las funciones ESNOD y COINCIDIR. De esta forma, la función COINCIDIR devuelve una matriz de «1», «2» y «N/D» que se corresponden con cada valor de la columna C. En este sentido, los «1» corresponden a aquellos valores iguales al valor en F4, los «2» corresponden a aquellos valores iguales al valor en F5 y por último los «N/D» corresponden a aquellos valores distintos del rango en F4:F5. Luego, la función ESNOD nos permite convertir aquellos valores «N/D» a «VERDADERO» y aquellos valores que sí aparecen en la columna C a «FALSO». Finalmente la función SUMAPRODUCTO permite combinar ambas matrices por medio de una multiplicación que genera una sola matriz de unos y ceros, donde los unos corresponden a aquellos valores que deben ser contabilizados (ya que en ambas matrices iniciales el resultado fue distinto de cero) y donde los ceros deben ser descartados.

De esta forma, logramos obtener de forma rápida y simple la cantidad de filas que cumplen con más de una condición específica incluyendo condiciones excluyentes o negativas dentro de un rango en Excel.

Si necesitas contar filas con una única condición te recomiendo ver el siguiente video: Contar filas con condiciones en Excel

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.