Microsoft Excel implementó las funciones CONTAR.SI y SUMAR.SI que realizan cuentas o sumas de con una condición simple sobre una única columna, pero muchas veces esto nos es poco por lo que nos vemos obligados a concatenar columnas así definir una única condición para realizar los cálculos necesarios.
Existe una forma un poco mas rebuscada que permite contar y sumar datos de filas con condiciones múltiples, es utilizando funciones matriciales.
Como ejemplo se tomará la lista que se muestra a la derecha.
Si queremos sumar la cantidad de kilogramos que se solicitaron en Febrero, o sea sumar los datos de la columna D cuando en la columna A contiene febrero la forma tradicional es:
La forma utilizando funciones matriciales es:
Poniendole nombre a cada uno de los parámetros se ve de la siguiente manera:
¿Cómo funciona esto?:
Las condiciones se definen de la forma:
RANGO_CONDICION ="Valor"
cuyo resultados posibles son VERDADERO o FALSO según se cumpla o no la condición, podemos utilizar los operadores =, >, >=, <, <=, o <>.
El valor VERDADERO es representado con un 1, y el FALSO con un 0, por lo que al multiplicar este resultado por el RANGO_A_SUMAR (en este caso D2:D27) por cada fila que cumple la condición suma el obtiene el dato de la columna a sumar.
Por último con SUMAPRODUCTO se suman todas las filas verdaderas obteniendo el resultado final.
Ahora si queremos sumar con dos o mas condiciones, pondremos el producto de tantas condiciones como queramos y al final lo multiplicamos por el valor a sumar:
Aquí se están validando 3 condiciones: que los elementos del primer rango sean igual a "Valor1", y que los elementos del segundo rango sean mayores a "Valor2", y que los elementos del tercer rango sean diferentes de "Valor3". De cumplirse las 3 condiciones sumamos los datos.
También se pueden combinar los resultados, por ejemplo si tenemos que sumar el contenido de dos, o más, columnas cuando cumplen condiciones múltiples lo podemos hacer de la siguiente manera:
Cuando se desea contar registros que cumplen con estas condiciones múltiples, éstas se deben de multiplicar por 1, o anteponer - (dos signos de menos).
ó
A continuación se presentan varios ejemplos:
Contar filas que cumplen con más de una condición (CONTAR.SI con múltiples condiciones):
Cantidad de pedidos de Naranjas que realizó Luis en febrero:
Resultado: 2.
Sumar el producto de dos columnas de filas que cumplen con más de una condición (SUMAR.SI con múltiples condiciones):
Sumar el importe que gastó Luis durante el mes de Febrero:
Resultado: 67.
Contar filas cuya condición es calculada con varias de una columnas. Cantidad de pedidos con importes mayores a 30:
Resultado: 6.
Otros mas:
Cantidad de pedidos de Naranjas que compró Luis en Enero:
Resultado: 0
Cantidad kg de Naranjas que compró Luis en Enero:
Resultado: 0
Cantidad kg de Naranjas que compró Luis en Febrero:
Resultado: 5
Cantidad de pedidos de 3 o mas kilogramos:
Resultado: 8
Importe de los pedidos de 3 o mas kilogramos realizados en marzo:
Resultado: 114.
Cantidad de ventas de 1 kilogramo cada una:
Resultado: 7.
Importe total de los pedidos con importes mayores a 30:
Resultado: 247
Entradas
12 comentarios:
Tengo problemas si entre los criterios a cumplir de la fórmula SUMAPRODUCTO, uno de ellos hace alusión a una Fecha. Me da error. Si sustituyo la fecha por un texto y coloco ese texto en la fórmula, si me lo filtra.
????
Cuando se trata de comparaciones con fechas hay que tener cuidado con los formatos, para evitar problemas es recomendable crear la fecha con la función FECHA, o utilizar una fecha ya definida en alguna celda.
Armando la fecha:
=SUMAPRODUCTO(($A$2:$A$10=FECHA(2009;2;25))*1)
Tomando la fecha de otro lado:
=SUMAPRODUCTO(($A$2:$A$10=$C$1)*1)
me atoro porque necesito saber como hacerle para que me compare solamente si coincide el mes para poder sumarlo
La manera de comparar solo por el mes (un único campo) está descripta al comienzo de este post. Tienes dos formas disponibles: la mas simple utilizando la función SUMAR.SI, o de lo contrario utilizando la función SUMAPRODUCTO.
Si lees esta respuesta, y continúas con problemas no dudes en solicitar mas ayuda.
Hola, estoy haciendo unos reportes de solicitudes en los cuales manejo horas y necesito saber cuales solicitudes se hicieron de día y cuales de noche, he intentando con varias fórmulas pero no me da los resultados.
En la columna B7:B36 tengo horas en formato 00:00 AM o 00:00 PM. Intenté con =SUMAPRODUCTO((B7:B36 >HORANUMERO("06:00 AM"))*(B7:B36 <HORANUMERO("05:00 PM")*1) pero me resulta 0, también lo hice quitándole el HORANUMERO y continúa dando cero...
El detalle es que esa columna se llena de acuerdo a una consulta a una BD por lo tanto el rango lo puse de B7:B236 ya que no habrán más de 236 datos...
Si me puede ayudar le agradecería muchísimo ya que no soy experta en excel y llevo varios días buscando la solución...
Gracias
Vas bien, solo tienes un problema con los paréntesis que separan las condiciones.
En la segunda condición, cierra el paréntesis antes de multiplicarlo por uno “* 1”, o directamente elimina “*1” que no es necesario cunado tienes mas de una condición.
Cualquier cosa me avisas.
Continua sin funcionar! También intenté con =SI(Y(C8>=$F$1, C8<$G$1), 1, 2) y F1 yG1 puse las horas pero no sirve! siempre me da como si fuera falso! Ya le cambié el formato hice todo tipo de pruebas y continúa sin funcionar!
Xinia,
La formula que hiciste al inicio está bien (salvo ese pequeño detalle), yo simplemente la pegué y funcionó.
Me la juego que el problema son los datos a contar, si bien tu tienes los datos con formato de hora es muy probable que también contengan información de una fecha. Lo compruebas poniéndole al menos a una celda el formato personalizado dd/mm/yyyy hh:mm (puede que en lugar de yyyy requieras aaaa).
Si es así, estás comparando una fecha desde hace dos mil años (si pones solo la hora será del año cero) con una actual (recuerda que Excel siempre maneja Fecha y Hora para todo lo que se trate de fecha y/u hora).
Esto lo solucionamos extrayendo la información de la hora y comparando solo por este dato:
=SUMAPRODUCTO((HORA(B7:B36)>=6)*(HORA(B7:B36)<17)*1)
Cambiamos la condición un poco, tomando como de día la parte 6:00 exacta (que antes no lo tenía), y que sea menor a las 17 horas.
Me avisas
Gracias! Así si me funcionó pero si lo hago al contrario (para averiguar cuantas solicitudes se hicieron en el turno de la noche) el resultado es cero... entonces quería hacer una resta del total de solicitudes menos las diarias e hice un =CONTAR(B7:B36) pero me da cero, alguna otra idea?
Muchas gracias por su ayuda con esa fórmula me funcionó, pero si le daba vuelta para calcular las solicitudes en el horario nocturno no me funcionó, encontré una función para sacar el total: =CONTARA(B7:B881) entonces le resté la suma de solicitudes diarias al total y me funcionó a la perfección. De verdad! muchísimas gracias!
Xinia, aunque ya solucionaste tu problema, para la próxima te comento:
En la condición original estás combinando ambas condiciones con el signo de multiplicación que a nivel lógico corresponde al operador lógico AND (función Y) ya que se deben de cumplir ambas condiciones.
Para la función inversa debes de comparar mediante el operador lógico OR (función O) y en lugar de multiplicar debes de sumar ambas condiciones. De esta manera se cuenta en el caso de que se cumpla cualquiera de las dos condiciones.
Este es un ejemplo de contar las horas menores a las 6 de la mañana o posterior o iguales a las 5 de la tarde.
=SUMAPRODUCTO(((HORA(A3:A26)<6))+((HORA(A3:A26)>=17))*1)
OJO: No funciona si se cumplen ambas, pero en tu caso nunca se dará.
Aquí tienes otro problema de que si la celda no tiene nada te la va a contar como fuera del rango, como en tu caso las horas llevan una fecha podrías agregarle la validación que la celda sea mayor que cero (en el caso de no llevarla queda afuera las doce de la noche).
=SUMAPRODUCTO(((HORA(A3:A26)<6))+((HORA(A3:A26)>=17))*(A3:A26>0))
La manera menos complicada y sin fallas es negar la condición original con el operador lógico NOT (función NO):
=SUMAPRODUCTO(NO((HORA(A3:A26)>=6)*(HORA(A3:A26)<17))*1)
Y por supuesto otra opción es la que hiciste restándole a la cuenta total.
De esas formas no me funciona porque me suma las celdas que estén vacías y tengan formato de hora... pero igual muchas gracias por la ayuda, con sumar el total y restarle la formula que usted me dio me salen los datos correctos. Saludos!
Publicar un comentario