Sumproduct with Year

  • I have a column with years, blanks, and formulaic blanks (if false, than ""). I am running a sumproduct to identify a cash flow tied to a matching year. because some of the cells are not a serial number (or date), I am #Value!. Any ideas for how to resolve this error (I already tried iferror, but it makes the whole "Year()" false)...

  • This is really a two part problem - see attached sample. I need to solve the year/value error issue, but I also need to figure out how to use sumproduct to reconcile a calendar year. Cash flows bump up at a midway point in the year, so I need to divide the lower cash flow allocated to the first "half" of the year (not a true half, just the portion of the year that precedes the bump up date), and the latter "half" that includes the bumps, for a full year reconciliation.


    Any help would be truly appreciated.

  • Hello,


    Thanks for your sample file ...


    However, could you expand a little ... and provide a detailed layout of all the Cash Flows numbers related to the "first half" of the year ...


    as well as the Number you are expecting as the final result generated by your Sumproduct formula ...


    Cheers

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)


  • As always Thank you Carim!


    Please see the attached updated sample file - the goal is to use sumproduct to identify the correct widget (in a much longer list in the final model) reconcile the year end cash flows by dates (not sum the widgets)


    Thank you again in advance!


  • As always Thank you Carim!


    Please see the attached updated sample file - the goal is to use sumproduct to identify the correct widget (in a much longer list in the final model) reconcile the year end cash flows by dates (not sum the widgets)


    Thank you again in advance!

  • Hello Daniel,


    Sorry but I am still confused about your goal ...


    What are exactly the steps (and the required calculations) to achieve your goal ... :duh:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello Daniel,


    Sorry but I am still confused about your goal ...


    What are exactly the steps (and the required calculations) to achieve your goal ... :duh:


    Thanks Carim, as always your assistance is appreciated.


    I have updated the same to include a "proforma" in a method I have used in the past that does work, but isn't flexible enough to achieve future goals.


    I have also included the steps. Hopefully, this explains the goal.

  • Thanks for your updated file ... As soon as I have a moment ... I will dive into your spreadsheet ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks for your updated file ... As soon as I have a moment ... I will dive into your spreadsheet ... :wink:


    I just noticed an error in the steps description it should've said: "Steps 2 ===> Identify corresponding cash flow in column K, by "Year" reference in column F multiplied by its appropriate yearfrac"

  • Hi again,


    Attached is a proposal - Version 2 - in order to dynamically generate your Results Table ...


    Hope this will help


    Thanks for your help!


    But unfortunately, this is similar to what I already had. The reason, I need it to be more flexible is, say a widget's cost increase is not grown by percentage; but rather per unit (i.e. rather than dollar*(1+X.XX%)^y-periods. We have Quantity*$X.XX=dollar+(Quantity* $x.xx^Y-periods. The Y-period needs to be able to non-consistent/systematic, so the first period might be five years, but the second or third might be 3 or 1 or 7, etc). At the end of the day, what I really need is a way to lookup the widget and the year, and apply the yearfrac, since the month is not likely to change, only the year and period...


    Sorry if this is confusing...

  • To be quite honest with you ... this is indeed quite confusing ... :confused:


    However, in order to give it a try ... to get closer to your goal ...


    would you mind attaching an updated file which would precisely reflect your differentiated costs increases ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • To be quite honest with you ... this is indeed quite confusing ... :confused:


    However, in order to give it a try ... to get closer to your goal ...


    would you mind attaching an updated file which would precisely reflect your differentiated costs increases ... :wink:


    Carim, I always appreciate your help (and I have learned a lot from you) I just thought I would share the solution, that I got to work. Please see the attached.