none
Cumulative SUM as of an specific date within the year of the parameter value

    Question

  • Hello,

    I have a worksheet that keeps track of sales for the past few years including the current year. I would like to be able to get the YTD sum based on the date value entered on a specific cell.

    In the following example I wanted to know the total sales as of the reporting period entered on cell D4. The formula should be able just give the sum for the year of the date value entered on cell D4. In this example the result represents the sum from January to April = $400.00.  If the date on cell D4 would have been October 31, 2012 then the sum would have been $1,000.

    Thank you for your help!

    • Edited by Databasico Saturday, June 22, 2013 1:44 AM Update Image
    Saturday, June 22, 2013 1:40 AM

Answers

  • Hi,

    You can achieve this by using Array Formula: (do not forget to press CTRL+SHIFT+ENTER after pasting the below formula)

    =SUM(IF($A$7:$A$19<=D4,IF(YEAR($A$7:$A$19)=YEAR(D4),$B$7:$B$19,0)))


    Vish Mishra

    • Marked as answer by Databasico Sunday, June 23, 2013 4:31 PM
    Saturday, June 22, 2013 8:18 AM

All replies

  • Hi,

    You can achieve this by using Array Formula: (do not forget to press CTRL+SHIFT+ENTER after pasting the below formula)

    =SUM(IF($A$7:$A$19<=D4,IF(YEAR($A$7:$A$19)=YEAR(D4),$B$7:$B$19,0)))


    Vish Mishra

    • Marked as answer by Databasico Sunday, June 23, 2013 4:31 PM
    Saturday, June 22, 2013 8:18 AM
  • Vish,

    Thank you so much for your prompt response. The formula worked great. 

    I just have a follow up question.  I use a list on cell D4 with all the dates value already pre-populated on a separate worksheet. I do this for field validation purposes to force the user to use the precise date values on the list. Since we use the last day of the month we want to make sure they enter the correct value otherwise the calculation will not work for example if they enter 5/30/2013 instead of 5/31/2013.

    I noticed that once I select a date from the drop-down menu the formula field does not update. I created a macro with a button that basically refreshes the formula field by performing CTRL+SHIFT+ENTER.

    My question is: Is there a way to program the formula field to refresh dynamically once the date value form the list on D4 changes without the need of a macro button?

    Thank you so much for your time.

    Regards,

    Manuel

    Sunday, June 23, 2013 2:38 AM
  • Hi Manuel,

    Formula should get automatically refreshed as soon as you select a new date in D4 list. No need of any macro as such. 

    Can you share your workbook, removing all kind of sensitive data and putting some dummy values, so that i can look in to it.


    Vish Mishra

    Sunday, June 23, 2013 10:13 AM
  • Hi Vish,

    Is there a way to attach an Excel file on this forum? I'll be glad to send you the sample file that I am working with. It is very simple. I am using MS Excel 2010.

    Thanks!

    Sunday, June 23, 2013 5:03 PM
  • Hi,

    Can you check the Automatic Calculation option in your workbook?

    File > Options > Formulas > Workbook Calculation > Automatic


    Vish Mishra

    Sunday, June 23, 2013 5:45 PM
  • That was it. You got it!

    Thank you so much!

    Regards,

    Manuel

    Sunday, June 23, 2013 5:52 PM