jueves, enero 29, 2009

La función SUMAPRODUCTO con fechas

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:

32 comentarios:

  1. 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.

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

    ResponderBorrar
  3. 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.

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

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

    ResponderBorrar
  6. 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

    ResponderBorrar
  7. 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

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

    ResponderBorrar
  9. 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

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

    ResponderBorrar
  11. 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?

    ResponderBorrar
  12. 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.

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

    ResponderBorrar
  14. 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

    ResponderBorrar
  15. 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.

    ResponderBorrar
  16. 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

    ResponderBorrar
  17. 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.

    ResponderBorrar
  18. 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

    ResponderBorrar
  19. 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

    ResponderBorrar
  20. 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

    ResponderBorrar
  21. 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.

    ResponderBorrar
  22. Jose Francisco Velez06 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

    ResponderBorrar
  23. 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.

    ResponderBorrar
  24. 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

    ResponderBorrar
  25. 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)

    ResponderBorrar
  26. 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!

    ResponderBorrar
  27. ¿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.

    ResponderBorrar
  28. 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?

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

    ResponderBorrar
  30. CODIGO MATERIALES ENERO
    YO NECESITO BUSCAR ESTA INFORMACION EN UNA TABLA LA CANTIDAD DE CADA UNO DE ESTOS PRODUCTOS Y POR FECHA
    NUMERO PRODUCTO ENERO FEBRERO MARZO ABRIL
    1 AGUA
    2 ALMOHADILLAS
    3 ALMOHADILLAS D4
    4 ARCHICOMODOS
    Y ESTA ES LA BASE DE DATOS:
    FECHA MES CODIGO MATERIALES CODIGO GERENCIA GERENCIA UNIDADES
    25/06/2019 junio 1 AGUA 1 ADMINISTRACION 2
    25/05/2019 mayo 10 BLOCK DE NOTAS CON ESPIRAL 1 ADMINISTRACION 2
    10/05/2019 mayo 25 CARPETAS TIPO KIMBERLY 1 ADMINISTRACION 2
    01/01/2019 enero 10 BLOCK DE NOTAS CON ESPIRAL 1 ADMINISTRACION 2
    10/06/2019 junio 1 AGUA 1 ADMINISTRACION 2
    05/01/2019 enero 12 BORRADOR DE PIZARRA ACRILICA 1 ADMINISTRACION 2
    4
    COMO PUEDO BUSCAR AGUA EN UN MES ESPECIFICO Y QUE ME TRAIGA CUANTAS SOLICITARON

    ResponderBorrar
  31. Te sugiero que uses una tabla dinamica

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.