La función SUMAPRODUCTO con fechas

jueves, enero 29, 2009

La función SUMAPRODUCTO nos permite, entre otras cosas, contar y sumar con más de una condición. Sin embargo existe un problema cuando queremos usar fechas como criterio.
Supongamos esta tabla con dos columnas: criterio 1 contiene los valores "si" o "no" y criterio 2 contiene fechas que van del 01/01/2009 al 10/01/2009



Queremos contar cuantas celdas en el rango A2:A11 cumplen las siguientes tres condiciones: el valor de la celda es "si" y la fecha de la celda correspondiente en la columna B es mayor del 05/01/2009 y menor del 10/01/2009

Si usamos esta fórmula

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>05/01/2009)*(B2:B11<10/01/2009))

veremos que el resultado es 0 y que Excel a modificado la fórmula de esta manera

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>5/1/2009)*(B2:B11<10/1/2009))

No sólo lo ceros han desaparecido sino que Excel interpreta, por ejemplo, 5/1/2009 como operación aritmética cuyo resultado es 0.00248880039820806.

Para obligar a Excel a considerar las fechas como criterios tenemos tres posibilidades:

1 - usar el número de serie de la fecha. Por ejemplo, en lugar de B2:B11>05/01/2009 usaremos B2:B11> 39818. Para saber cuál es el número de serie correspondiente a una fecha podemos seleccionar la celda con la fecha, abrir el menú Formato de Celda y apuntar a Números a la opción General



2 - usar la función FECHA. En nuestro ejemplo la función sería

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>FECHA(2009,1,5))*(B2:B11<FECHA(2009,1,10)))


3 - usar referencias a celdas que contengan las fechas que queremos usar como criterio. En nuestro ejemplo, si la fecha del primer criterio la ponemos en la celda C1 y la segunda fecha en la celda C2, la fórmula sería

=SUMAPRODUCTO((A2:A11="si")*(B2:B11>C1)*(B2:B11<C2))


Esta última opción es la preferible ya que nos permite determinar los criterios dinámicamente sin necesidad de modificar la fórmula.

Technorati Tags:

31 comments:

Doc_virus 30 enero, 2009 00:19  

Muy buenas tardes Jorge.

Tengo una pregunta que quizá pudieras contestarme, existe alguna forma de expresar en la función contar.si que los datos a evaluar sean mayores o menores que cero.

He intentado con esta forma pero no devuelve el resultado que busco:

=contar.si(a1:A10,"<>0")

Gracias y felicidades por tu blog.

Jorge L. Dunkelman 30 enero, 2009 07:53  

De acuerdo a como está escrita la fórmula, tendría que funcionar. Debe haber algún otro problema.

Anónimo,  02 febrero, 2009 23:19  

hola, Como puediera hacer para encontrar el promerio de un valor segun dos fechas.

ejemplo:
si tengo en a1=2.5 a2=3.7 a3=5.9 y en b1:B3 fechas quiero el promerio de a1 y a3 como seria la formula.

Jorge L. Dunkelman 03 febrero, 2009 21:41  

Primero tendría que entender qué significa promediar cantidades con fechas. Tal como está planteado, no encuentro la lógica en la pregunta.

giuliano_ 12 marzo, 2009 01:49  

jorge, hola!
ojalá me puedas ayudar... revisando en la web para tratar de encontrar una solución econtré este blog... que por lo demás, esta buenísimo...

necesito sumar datos de una tabla, pero respetando más de una condición:
1º que esten entre un rango de fechas definidas desde dos celdas externas a la tabla, pero dentro de la misma hoja, en donde escribo una fecha de inicio y otra de término.
2º y que dentro de ese rango me sume las cantidades que acompañan a un producto determinado, también definido desde una celda externa a la tabla, pero dentro de la misma hoja

a modo de ejemplo, para ser más explicativo... la función que busco tendría que sumar del cuadro de abajo las cantidades que correspondan al "ITEM 3" entre dos fechas, como por ejemplo: entre el 01/04/2009 y el 30/04/2009...

FECHA PRODUCTO CANTIDAD
01/01/2009 ITEM_1 200
16/01/2009 ITEM_2 300
31/01/2009 ITEM_2 400
15/02/2009 ITEM_2 500
02/03/2009 ITEM_3 600
17/03/2009 ITEM_3 700
01/04/2009 ITEM_3 800
16/04/2009 ITEM_3 900
01/05/2009 ITEM_4 1.000
16/05/2009 ITEM_5 1.100
31/05/2009 ITEM_5 1.200
15/06/2009 ITEM_5 1.300
30/06/2009 ITEM_6 1.400
15/07/2009 ITEM_6 1.500
30/07/2009 ITEM_1 1.600

en este ejemplo, el resultado debería ser 1.700, pero no encuentro una función que me ayude... sumar.si.conjunto es la más cercana a mis pretenciones, pero debo estar cometiendo un error al definir los criterios porque no salgo del error...

las celdas que me indican las condiciones de fechas me las entregan dos celdas que estan en la parte superior que se modifican de acuerdo a las necesidade de consulta, y la celda que me entrega el criterio o condición del producto está con una lista desplegable... no creo que influyan, pero te lo comento por si acaso...

espero puedas atender mi consulta, gracias!

Jorge L. Dunkelman 12 marzo, 2009 06:39  

Tienes que usar SUMAPRODUCTO tal como muestro en esta nota. Si quires puedes mandarme el archivo por mail.

Anónimo,  26 abril, 2009 12:11  

Hola Jorge,

Aunque ya tengas resuelto el problema de la fecha con la función fecha o con referencia a una celda , un pequeño añadido es que también se puede utilizar fechanumero (datevalue).
Por ejemplo reemplazamos FECHA(2009;1;5)por FECHANUMERO("05-01-2009")eso sí, con las comillas, y funciona igual.

Un saludo,
Sergio

mauri 12 junio, 2009 04:15  

hola jorge
introduzco esta formula en exel y me da como resultado cero
=SUMAPRODUCTO($B$2:$B$12784=$M$3;$A$2:$A$12784=L11;$J$2:$J$12784)
cual puede ser el error??
gracias

Jorge L. Dunkelman 12 junio, 2009 07:00  

Mauri
tendría que saber cuáles son los valores de las celdas de los rangos. Mandame el archivo por mail.

Anónimo,  17 septiembre, 2009 05:15  

Navegando por aquí y por allá llegue a este tu sitio, leí algunas cosas que me llamaron la atención como esta entrada de SUMAPRODUCTO, la examine y aprendí dos cosas:

1.- *significa el condicional Y, si se pone entre paréntesis que también sirve para algunas otras formulas como
=si((condicion1)*(condicion2),verdadero,falso)

2.- Me ayudo a automatizar un proceso que he hecho por la vía larga desde hace como un mes, por el tiempo en el trabajo no había investigado la de hacerlo en menos pasos, pero con tu entrada ahora no solo es en menos pasos, si no que es tan solo con una formula!


Mil gracias por tus valiosas aportaciones

Felipe Vargas 03 marzo, 2010 15:51  

Jorge,
excelente solución para sumar con criterios, muchas gracias

Santiago,  11 marzo, 2010 03:48  

Hola, tengo dos columnas:
A: B:
11/1/2010 4
13/1/2010 2
21/1/2010 0
29/1/2010 1
2/2/2010 3
7/2/2010 5
10/2/2010 3
11/2/2010 4
16/2/2010 2
19/2/2010 6
21/2/2010 2
23/2/2010 0
24/2/2010 0
3/3/2010 1
5/3/2010 3
Necesito calcular en celdas aparte el promedio de las celdas de la columna B para los meses de enero, febrero, marzo y subsiguientes, del año 2010 y subsiguientes.
Hay alguna forma?

Santiago,  13 marzo, 2010 00:31  

Jorge, muchas gracias por tu pronta respuesta. Seguí tu indicación y no conseguí avanzar. Siguiendo con mi ejemplo anterior de las 2 columnas, necesito calcular el promedio para los números de la columna B que tengan fechas (en la columna A) correspondientes al mes de enero/2010 (en el ejemplo es el promedio de 4;2;0;y 1) y de los meses y años subsiguientes.
Por lo que necesito que la condición sea para la columna A, pero el promedio me lo calcule de la columna B.

Jorge L. Dunkelman 13 marzo, 2010 08:53  

Santiago,
te sugiero me mandes el archivo con una descripción del problema (fijate en las condiciones que aparecen en el enlace Ayuda).

Anónimo,  06 abril, 2011 18:47  

JCS
Hola buenas tardes, quisiera me apoyaran con lo siguiente:
necesito realizar una suma diaria dependiendo del agente de ventas, ya intente realizarla con la funcion suma producto pero no me respeta el nombre, ayudenme¡¡

FECHA IMPORTE
01/03/2011 3,593.68 ARTURO
01/03/2011 3,944.80 ARTURO
01/03/2011 39,478.41 ARTURO
02/03/2011 4,524.00 PEDRO
02/03/2011 2,873.00 PEDRO
03/03/2011 4,611.00 JOSE LUIS

TOTAL DIA Y VENDEDOR

Jorge L. Dunkelman 06 abril, 2011 18:57  

Suponiendo que estás construyendo la fórmula correctamente, te sugiero revisar si no hay espacios en balco en algunas de las celdas con los nombres. Para el caso "ARTURO" y "ARTURO " no son iguales (el primero tiene un largo de 6 y el segundo 7).
Además, me parece que sería más eficiente usar una tabla dinámica, en especial si hay muchos datos en la base de datos.

Anónimo,  06 abril, 2011 19:20  

JCS
Gracias por tu respuesta, ya intente acomodar de igual manera los nombres, y tampoco, quisiera realizar una tabla dinamica, pero lo que intento es que mediante esa funcion se rellene otra tabla, algo asi, espero me entiendan que es lo que quiero
VENTAS MENSUALES
ARTURO PEDRO JOSE LUIS
01/03/2011
02/03/2011
03/03/2011
04/03/2011
Requiero que mediante la suma me diga cuanto vendio cada uno en determinado dia del mes

Agradeciendo de antemano

saludos

Jorge L. Dunkelman 06 abril, 2011 23:51  

Te sugiero que me envíes el archivo y una explicación de lo que quieres hacer. La dirección del mail se encuentra en el enlace Ayuda.

Miren 04 julio, 2011 13:37  

Hola! Siguiendo con la función SUMAPRODUCTO, he hecho lo que habéis comentado, y las fechas las tengo en otras celdas. Pero tengo un problema: Cuando hago (B2:B11>FECHA(2009,1,5))*(B2:B11<FECHA(2009,1,10)) no me coge solo las fechas de esos 5 días, si no que todas las fechas que tengo en las celdas que he seleccionado. ¿Qué puedo hacer?


Gracias

Miren 04 julio, 2011 13:38  

Hola! Siguiendo con la función SUMAPRODUCTO, he hecho lo que habéis comentado, y las fechas las tengo en otras celdas. Pero tengo un problema: Cuando hago (B2:B11>FECHA(2009,1,5))*(B2:B11<FECHA(2009,1,10)) no me coge solo las fechas de esos 5 días, si no que todas las fechas que tengo en las celdas que he seleccionado. ¿Qué puedo hacer?

Gracias

Miren 04 julio, 2011 13:39  

Hola! Siguiendo con la función SUMAPRODUCTO, he hecho lo que habéis comentado, y las fechas las tengo en otras celdas. Pero tengo un problema: Cuando hago (B2:B11>FECHA(2009,1,5))*(B2:B11<FECHA(2009,1,10)) no me coge solo las fechas de esos 5 días, si no que todas las fechas que tengo en las celdas que he seleccionado. ¿Qué puedo hacer?

Gracias

Jorge L. Dunkelman 04 julio, 2011 14:42  

La fórmula, tal como pones, no tiene ningún problema y tendría que darte los resultados esperados (4 en tu caso). Si quieres puedes mandarme el archivo para que vea donde pueda estar el problema.

Jose Francisco Velez,  06 julio, 2011 06:10  

Hola Jorge....como siempre que BLOG tan vacano.

Tengo una duda y he buscado en todos los articulos tuyos y hasta el momento.....nada.

Resulta que estoy trabando con graficos dinamicos y me gustaria vincular el titulo del grafico al campo de la tabla dinamica que uso como filtro (este campo lo coloco en "filtro del informe"), ya que cada que que selecciono un filtro distinto, los datos se actualizan, sin embargo de manera manual tengo que cambiar el titulo.

Estoy de acuerdo con vos cuando mencionas que las tablas dinamicas son una excelente manera de analizar información

De antemano mil gracias y suerte

Jorge L. Dunkelman 06 julio, 2011 07:25  

Lo que podés hacer es ligar el cuadro de texto que contiene el título a la celda donde reside el filtro de la tabla. Seleccionás el cuadro y luego en la barra de las fórmulas ponés "=" y la dirección de la celda (o clic con el mouse a la celda).
Podés ver un ejemplo de esta técnica en esta nota.

Juan Eduardo Gonzales Sandoval 17 febrero, 2012 21:43  

Hola Jorge, si puedes ayudarme con el problemita que tengo.
tengo una base de datos en el cual registro los viajes, galones consumidos, kilometros recorridos, etc de cada uno de mis volquetes por dia. y lo que necesito es una fórmula que me permita sumar los km recorridos de una determinada unidad de acuerdo a una fecha especificada. no se si puedes ayudarme lo hago con tabla dinamica pero preferiría hacerlo con una fórmula.
Gracias

Jorge L. Dunkelman 18 febrero, 2012 07:55  

Justamente con SUMAPRODUCTO como muestro en la nota. Suponiendo que tus datos están organizados en una tabla y que en la columna A están las fechas, en la B los volquetes y en la C los kilómetros (es decir que cada fila muestra cuantoskilomtros hizo un volquete en una fecha determinada), y suponiendo que el rango de datos va de la fila 2 a la fila 1000 (la fila 1 contiene los encabezamientos de las columnas), la fórmula sería

=SUMAPRODUCTO((A2:A1000=la fecha buscada)*(B2:B1000=el volquete buscado)*C2:C1000)

Anónimo,  28 junio, 2012 16:34  

Hola Jorge!
Como podria hacer para que esta formula no me detectase las celdas #¡VALOR!?
=SUMAPRODUCTO(($M$21:$M$5000=N5)*($E$21:$E$5000>=0)*($H$21:$H$5000))

Las celdas #¡VALOR! al final de una gran matriz en la columna H estan camufladas por la formula:
=SI(ESERROR(E21*INPUTS!$G$9-G21);"";E21*INPUTS!$G$9-G21)

Muchas gracias!

Jorge L. Dunkelman 29 junio, 2012 18:45  

¿Por qué no usar SI(ESERROR...) o mejor aún SI.ERROR (Excel 2007/10), junto con SUMAPRODUCTO?
El error proviene de usar "" como resultado si la fórmula da error. SUMAPRODUCTO no puede multiplicar valores que no sean numéricos. Tendrías que reeemplazar "" por 0.

Anónimo,  13 octubre, 2012 16:17  

Hola,

Quiero hacer esto:

=SUMAPRODUCTO((E1:E1000=FECHA(2010;4;30))*(F1:F1000 >="10:00:00");H1:H1000)

Pero, no responde correctamente. Donde esta un error?

Jorge L. Dunkelman 14 octubre, 2012 07:00  

=SUMAPRODUCTO((E1:E1000=FECHA(2010;4;30))*(F1:F1000 >=HORANUMERO("10:00:00")*H1:H1000)

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP