jueves, 21 de enero de 2010

Función SUMAR.SI para calcular saldos no vencidos.

Planteamos un nuevo ejemplo de la función SUMAR.SI, de acuerdo a la consulta de un lector:
...Estoy realizando una tabla con unas fechas, importes y vencimientos:
· columna A: fechas
· columna B: vencimiento de los importes
· columna C: importes
· y en una celda, por ejemplo F4, una suma de los importes de la columna C que no estén vencidos aún.
Es decir los que ya hayan pasado de esa fecha, ya no se sumen en F4, solo los que estén por vencer. Supongo que tendría que combinar de alguna manera la columna B con la C pero no se con qué formula. ...

Partimos del siguiente listado de facturas con campos de información: 'Fecha factura', 'Fecha vencimiento' e 'Importe':


Vamos a calcular cuál es el importe de las facturas aún no vencidas del listado, tomando como referencia la fecha del día presente.
Esta fecha la dejamos formulada con la función HOY(), que nos devuelve la fecha del día actual (si la función no actualiza la fecha, es posible que tengamos que cambiar la configuración que controla cuándo se recalcula el libro o la hoja de cálculo de manual a automático).
Sólo nos queda definir, de acuerdo a lo explicado en la entrada de este blog, los argumentos de la función. Para la función SUMAR.SI(rango; criterio; rango suma) en nuestro ejercicio serán:
  • rango: las celdas con las 'Fechas de vencimiento' $B$2:$B$11

  • criterio: '>='&F2
    es importante fijarse en cómo hemos planteado la condición!!; ya que de otro modo no lo reconocería.

  • rango suma: las celdas del campo 'Importe'

=SUMAR.SI($B$2:$B$11;">="&F2;$C$2:$C$11)


El valor que nos devolverá será la suma de los 'Importes' que cumplan la condición dada, i.e., cuya 'Fecha de vencimiento' sea mayor o igual a la 'Fecha actual'. En nuestro ejemplo la suma de los importes no vencidos es 12.717,00 eur.

18 comentarios:

  1. esta muy interesante la informacion soy de el salvador gracias...

    ResponderEliminar
  2. Hola buen dia,, necesito alguna formula que me de como resultado los dias que tiene vencida una factura, teniendo en cuenta que son 30 dias de credito solamente, Gracias

    ResponderEliminar
  3. Hola,
    puedes ver una solución que quedó subida hace algún tiempo
    http://excelforo.blogspot.com/2010/01/funcion-fechames-calculo-de.html
    con la función FECHA.MES podrás obtener el vencimiento a 30 días (=1 mes) dada una fecha.
    Slds

    ResponderEliminar
  4. Tengo un carpeta llamada contabilidad con un calendario en la cual llevo los gastos, tengo otra carpeta donde manejo los proveedores con sus respectivas facturas pero quiero que la hoja de contabilidad me extraiga las facturas de los proveedores por fecha y me sume los montos totales a pagar pero que a la hora de poner la fecha me los sume en el dia correspodiente.

    ResponderEliminar
  5. Por favor,
    seguramente sea por que acabo de regresar de vacaciones, pero no comprendo tu planteamiento.
    ¿puedes enviarme un fichero de Excel con un ejemplo a excelforo@gmail.com?

    Gracias

    ResponderEliminar
  6. EXCEL(lente) blog. Trabajo bastante con excel pero con los cambios constantes en mi vida laboral estoy un poco oxidado por decirlo asi.

    La formula está perfecta y muy bien explicado (no recordaba que podia hacer esto: ">="&F2), lo que me tiene aun pensando y no logro resolver es si deseo sumar entre rangos de fecha.

    Tomando el mismo ejemplo, si yo deseara sumar solo los importes del 2009 (en el caso del ejemplo seria C2 C3 C5), cual seria la formula a usar ?

    Te agradezco de antemano y sigo leyendo los demas temas.

    ResponderEliminar
  7. El esfuerzo tiene sus recompensas y contesto lo anteriormente consultado:

    {=SUMA(SI($C$7:$C$37>=$C$2;SI($C$7:$C$37<=$D$2;$H$7:$H$27;0)))}

    Donde la columna C7-C37 contiene las fechas, la columna H7-H37 los valores a sumarse y C2 y D2 son la fecha inicial y final del rango a ser consultado (lista desplegable en mi caso). Notese que va entre signos de conjunto (CTRL SHIFT ENTER).

    Me refresco mucho la memoria tu blog. Cuentas con un nuevo lector.

    Saludos.

    ResponderEliminar
  8. Hola JC,
    efectivamente, trabajar con funciones matriciales es una de las opciones por la que optar, otra sería una función de base de datos, echa un vistazo a:
    http://excelforo.blogspot.com/2009/10/otro-ejemplo-de-bdsuma.html
    también podrías haber desarrollado la función SUMAR.SI.CONJUNTO
    http://excelforo.blogspot.com/2010/02/sumas-condicionadas-sumarsiconjunto.html
    o incluso con tablas dinámicas.
    Me alegro de haber sido útil en tu sesión de refresco de conocimientos.
    Un saludo y gacias por tu comentario.

    ResponderEliminar
  9. si me pudieran ayudar necesito una formula que en base a la fecha actual me ponga los importes vencidos y por vencer de cada proveedor, que se actualice automaticamente conforme a la fecha actual.

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      Sólo sigue las indicaciones de esta entrada del blog para los no vencidos y con el criterio contrario (<=hoy() ) para los vencidos.
      Si además quieres añadir un nuevo criterio (en tu caso el 'proveedor') emplearías la función
      =SUMAR.SI.CONJUNTO

      Slds

      Eliminar
    2. Gracias, nada mas si me podrían decir como seria en ingles la formula porque yo tengo el Excel en ingles.

      mil gracias!

      Eliminar
    3. Hola,
      para ver las equivalencias de funciones español-inglés puedes leer estas entradas
      http://excelforo.blogspot.com.es/2013/05/vba-equivalencia-de-nombres-de.html
      y esta otra
      http://excelforo.blogspot.com.es/2013/05/nombres-de-funciones-de-excel-en-ingles.html

      En concreto, para tu caso
      SUMIFS = SUAMR.SI.CONJUNTO
      SUMIF = SUMAR.SI

      Slds

      Eliminar
    4. Mil gracias !!!

      Eliminar
  10. Respuestas
    1. Hola Yohana,
      como estás?, un gusto saludarte.
      Si concretas qué no has entendido, con gusto podría intentar aclararlo
      Un saludo

      Eliminar
  11. hola necesitaria armar una formula que me ponga el valor de la factura en dististas casilleros donde uno sea a 30 dias si esta en ese plazo, el mismo importe si ya vencio a 60 dias y si se paso que pase a otro casillero donde esta a mas de 90 dias. como podria hacer para que ese importe se pase de celda a medida que pasam los dias

    ResponderEliminar
    Respuestas
    1. Hola
      tendrías que montar la plantilla con la caída por días en columnas, por ejemplo...
      y luego por filas, y empleando condicionales comparar el vencimiento con esa caída de días..

      Espero te oriente la idea

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.