Muy buenas,
A menudo me veo en la tesitura de utilizar sumas condicionales, y muchas de estas veces, uno de los condicionantes es un rango de fechas. La funcion Sumar-si nos sirve para sumar un rango de valores que cumplan una condicion dada, pero si necesitamos que la suma evalue mas de una condicion, usaremos Sumar-si.conjunto.
Hoy vamos a ver como usar la funcion Sumar-si.conjunto de forma sencilla para poder utilizar fechas como evaluacion en la suma, es decir, para realizar la suma de un periodo en particular. Para ello tomamos una hoja como esta en la que tenemos lo siguiente:
Como veis, tenemos datos de facturas de distintas fechas y tambien con distintos tipos de IVA. Lo que vamos a hacer es un cuadro resumen abajo que nos va a sumar los totales (de base, de IVA, y de total) en funcion de la base y del trimestre al que pertenecen las facturas. En este caso el cuadro nos servirá para recopilar la informacion para el IVA o para contrastarla si la obtenemos de otra fuente (por ejemplo, un software contable).
Para ello hacemos un cuadro sencillo como el siguiente, sobre el que montaremos los sumar-si
Ahora queda lo complejo, montar la formula para que nos calcule todo (en este caso serán dos formulas, una por periodo). Para ello usamos como hemos dicho al formula sumar.si.conjunto, cuya sintaxis es así:
SUMAR.SI.CONJUNTO(rango_suma, rango_criterios1, criterios1, [rango_criterios2, criterios2], …)
En nuestro caso, será asi: (en este caso es la introducida en D19)
=SUMAR.SI.CONJUNTO(D$2:D$17;$B$2:$B$17;»<=»&FECHA(2015;3;31);$B$2:$B$17; «>»&FECHA(2014;12;31);$E$2:$E$17;$E19)
Que explicado por partes es así:
D$2:D$17 Seleccionamos las celdas a sumar
$B$2:$B$17;»<=»&FECHA(2015;3;31) La primera condicion es que la fecha sea inferior o igual al 31 de marzo de 2015
$B$2:$B$17;»>»&FECHA(2014;12;31) La segunda condicion es que la feca sea superior al 31 de Diciembre de 2014
$E$2:$E$17;$E19 La ultima condicion hace que el IVA sea el indicado en la fila de la foruma (en esta caso E19)
Podemos ver en la siguiente imagen que arrastrando esta formula de sumar-si a las celdas vacias del primer trimestre (y a las del segundo cambiando las fechas) nos hace el calculo. Os he resaltado en azul la unica celda que cumple las condiciones de la formula introducida en D19.
Espero que la explicacion haya quedado clara!
Un saludo
Últimamente he realizado operaciones con este tipo de algoritmos, pero cuando pongo rangos cerrados(«>=»,»<="), como ejemplo en un mes (del 1° de Diciembre al 31), me marcan varios errores, ¿es incorrecto usar rangos cerrados en las formulas de sumas de valores dentro de un periodo de tiempo?
Hola,
No, usar rangos cerrados no es un problema, la cuestión es con que «criterio» lo comparas. ¿Con una celda? ¿O con un valor? Si es con un valor, debes usar la formula FECHA dentro del SUMAR.SI.
Si quieres, dejanos tu formula y le echamos un vistazo.
Saludos
Hola, tengo tiempo utilizando esta fórmula y hasta hoy me doy cuenta que no me suman los valores del ultimo día del mes. Es decir, estoy sumando retiros de dinero pero no me suma los realizados el último día del mes, a pesar de que en mi fórmula tengo el «=»&FECHA(2019;C1;1);$A$23:$A;»=»&FECHA(2019;1;1);$A$23:$A;»<="&FECHA(2019;1;31);$B$23:$B;$A$20)
Hola, necesitaria saber que hay en la columna A, incluso un pantallazo del excel, ¿podrias compartirmelo?
Hola necesito calcular en un grafico la cantidad de gente que duerme en un lugar, pongamos por caso un hotel. Tengo la fecha de ingreso y de egreso de cada persona, el resultado final debe ser un grafico con fechas del mes completo en eje x y cantidad de personas durmiendo en cada día en el eje y
una manera que crei rapida de resolver fue: armar en filas el mes completo, dia por dia, e ir sumando 1 entre cada dia dormido por ejemplo
Carlos entro 06/08/2019 se fue 09/08/2019. deberia sumar 1 a la celda correspondiente a 6, 7, 8 y 9 de agosto
Alberto entro 07/08/2019 se fue 08/08/2019 deberia sumar 1 a la celda correspondiente a 7 y 8 de agosto
Betiana entro 08/08/2019 se fue 11/08/2019 deberia sumar 1 a la celda correspondiente a 8, 9, 10 y 11 de agosto
Bueno, esa formula nunca la logre. y quede trabado. puedes ayudarme?
desde ya muchas gracias
dario
Hola,
El problema es como se almacenan los datos. Una unica formula que cuente los dias, teniendo solo la fecha de entrada y salida sería una formula larguisima, ya que habría que incluir una casuistica para cada fila o intervalo de fechas. Es decir, una casuistica para cada reserva.
Sin embargo, si modificamos la forma de almacenar los datos, y en lugar de tener la fecha de entrada y salida, incluimos todas las fechas de la reserva, con un simple CONTAR.SI tendrias resuelto el problema.
Espero haber ayudado.
Saludos
Hola, requiero sumar usando dos condiciones, primero que sean de un mismo mes y segundo que cumpla con un criterio de categoría.
Ejemplo, tengo en una tabla ordenado por columna la fecha, el monto y la categoría, quiero sumar todo lo de el mes de enero que sea de la categoría «mantenimiento», ¿cómo ordeno la fórmula?
Hola Edgar,
Suponiendo que los datos estan asi: Fecha (Columna A), Monto (Columna B) y Categoria (Columna C), la formula sería la siguiente:
=SUMAR.SI.CONJUNTO(B2:B100;C2:C100;»Mantenimiento»;A2:A100;»<="&DATE(2019;1;31);A2:A100;">=»&DATE(2019;1;1))
Recuerda que para cerrar las fechas necesitas dos condiciones distintas.
Esto serviría para 100 filas, si necesitas mas sustituye todos los 100 por el numero de filas que necesites.
Saludos
Hola, necesito determinar un cuadro resumen en donde figuren cheques propios emitidos y que cuando llegue la fecha de pago, el valor desaparezca del cuadro resumen.
La base de datos tiene las siguientes columnas: Fecha de Pago (cuando se emite el cheque) – Valor Pagado – Modalidad de pago (aqui figuran si son cheques propios o de terceros) – Fecha de pago (cuando realmente puede cobrar el cheque).
La formula que utilice fue: SUMAR.SI.CONJUNTO(‘columna valor pagado;columna fecha de pago cuando se puede cobrar el cheque;»>=01/09/2019″; columna fecha de pago cuando se puede cobrar el cheque;»<=30/09/2019";modalidad de pago;"Cheque Propio")
Hasta ahí voy bien. Pero cuando voy a mi cuadro resumen, para saber cuantos cheques librados tengo para pagar, me sigue figurando los cheques del mes de septiembre. Vale aclarar que tengo un cuadro con todos los meses del año y otro al dia de hoy. Éste ultimo es el que necesito que figuren solo los cheque emitidos propios que tengo que pagar.
Saludos!
Hola Gabriela,
Entiendo el excel que tienes, pero no comprendo como necesitas los datos en el resumen.
Si quieres, compartenos tu excel, o incluye pantallazos para que lo veamos y te podamos ayudar mejor.
Quizás podriamos mezclar esta formula con la formula DATE (FECHA) y NOW(HOY) para ver que cheques futuros quedan por cobrar.
Saludos
JORGE, buenas tardes, tu explicacion esta muy bien. Solo quiero saber como le hago, quiero saber que tanto se sumo en ingreso por cliente. Estoy poniendo los apostrofles o las dobles comillas porque quiero subar cuanto se factura en UN MES de un CLIENTE, pero me sale lo de #¡VALOR!
Hola Luis,
Por favor, dejame aqui la formula que estas escribiendo para que pueda ver donde esta el error.
Gracias
Buenas tardes.
Quisiera agradecerte la valiosa información que has compartido. El hecho de escribir correctamente la fórmula me ha resuelto mi problema con valores acumulados hasta un fecha.
;»<=»&FECHA(2015;3;31);
Me gusta emplear nombres para una celda o rango en lugar de escribir el valor (2015:3:31).
p.e. "<="&fechas.
En este caso el nombre "fechas" hace referencia a una lista de fechas que tengo a la izquierda. Como quiero hacer el análisis por distintos países ( en horizontal ) tan solo copio la fórmula y me olvido de referencias absolutas o relativas.
fecha Spain Italy France Germany
01/04/2020 8.189 12.430 3.523 732
02/04/2020 9.053 13.157 4.032 872
03/04/2020 10.003 13.917 4.503 872
04/04/2020 10.935 14.681 6.507 1.017
05/04/2020 11.744 15.362 7.560 1.201
06/04/2020 12.418 15.889 8.078 1.293
07/04/2020 13.055 16.525 8.911 1.466
08/04/2020 13.798 17.129 10.328 1.720
09/04/2020 14.555 17.669 10.869 1.966
10/04/2020 15.238 18.281 12.210 2.232
11/04/2020 15.843 18.851 13.197 2.403
12/04/2020 16.353 19.470 13.832 2.532
13/04/2020 16.972 19.901 14.393 2.658
Voy a abusar un poco. Yo también tengo un problema que no consigo sacar.
La tabla viene siendo fecha, vehiculos, km
Consigo hacer un cuadro precioso donde cambiando el mes, me traslada los datos de lao vehiculos y me suma los km por dia y me marca los sabados y domingos en amarillo.
Problema, quiero usar una fórmula para que me sume los km de lunes a viernes. Pero al no estar fijas las columnas no puedo insertar una suma directa. Necesito un suma.si.diasemxxx. pero como hacerlo Mobile si al cambiar de mes la tabla empieza en un sabado ó en un miercoles. Como le expreso sumar la semana????
Hola Monica,
Sin saber como es el cuadro, se me ocurre que puedes añadir una columna con la formula DIASEM, y luego con un SUMAR.SI.
Ejemplo: Si en la Columna A tienes la fecha (y que hay encabezados), insertas en la columna B la formula =DIASEM(A2;2) y luego si quieres sumar los datos de la columna C con la formula =SUMAR.SI(B2:B100;<=5;C2:C100) Debería funcionar. Prueba y nos dices!
Buen dia Jorge, te comento una situacion que me surgio:
necesito saber el consumo de determinados insumos dentro de un rango de fechas:
ej. necesito saber cuanto maiz (entre otros) e consumido en un intervalo de fechas (ej: 01/01/2020 a 31/01/2020); teniendo en cuenta que los insumos se repiten a lo largo del periodo; estuve utilizando la funcion sumar.si.conjunto, pero al ingresar el rango de fechas a estas me las toma como un numero… la base de datos esta compuesta de la siguiente forma partiendo de la columna A:
IDCarga-Fechas-Hora-Receta ID-SETPoint-Insumos-cantidad
Hola Adrian,
En primer lugar, en Excel todas las fechas son un numero en realidad. Por eso se puede operar entre ellas.
Para usar un sumar si conjunto, con una de las condiciones (o dos como es tu caso) asociada a una fecha, puedes hacer lo siguiente:
=SUMAR.SI.CONJUNTO(Emitidas[IVA];Emitidas[Fecha];»<="&FECHA(2020;3;31);Emitidas[Fecha];">«&FECHA(2019;12;31);Emitidas[Tipo IVA];$F1040)
Como ves la formula esta vinculada a una tabla (formato tabla) y suma el IVA determinado en la celda F1040 entre el 1 de Enero y 31 de Marzo de 2020.
Espero que te sirva de ayuda!
Hola Jorge, necesito ayuda con mi tabla. Quiero que la formula Sumar.Si sume por fechas un rango de números pero me da cero pero el resultado cuando abro la función figura correcto
=SUMAR.SI(A3:D10;»>=»&»FECHA(H1)»;D3:D10)
A3;D10 Tabla completa sin encabezado ni totales
H1: contiene la fecha 01/09/2020
D3;D10: monto para sumar
Por favor me podrian ayudar, gracias
Hola Marcela,
Tienes que corregir la primera parte de la formula, ¿en que columna de la tabla están las fechas? SI fuese la A, entonces la formula sería «SUMAR.SI(A3:A10;…)
Saludos