Combining Monthly Fee Totals by month, year, and location with Annual Fee Increases

  • Hello OzGrid team,


    Thanks for being here. This one has my head spinning and I wonder if you can help me get closer to what I need. I've attached 2 screen shots showing almost all of the data set. I realized E23:E40 is obscured, but it's 2 character State Codes.


    We're trying to calculate the projected monthly revenue for each month and year in these tables. At first I starting going down the =SUMPRODUCT((E23:E40="TX")+(... and then I realized, I don't know how to determine the next steps. H23:H30 contains the anniversary date (date the fee can increase), and we list the fee by year 1, 2, 3, 4, 5 (3-5 are the same) in J22:J40, L22:L40, N22:N40, etc.


    So if client A has been there for 9 months, and client B has been there for 1 year and 2 months, we want to count each of their monthly amounts in the 1 year and 2 year columns respectively.


    Has anyone combined data in this manner before? I want the total of all of amounts in row 22:40, but I need it to grab the year 1 amount if it's <366 days from H23:H40.. Is using IFs inside of a sumproduct a thing? I think it's not, there's a syntax for if/or but I can't figure it out.


    Is SUMPRODUCT the wrong way to go?


    Thanks for any guidance you can provide.

  • Hello,


    Most probably ... using Sumproduct is, indeed, the right way to go ...


    BUT ...


    Images are dead Objects ... which will not help conveying your message ...


    Feel free to attach a sample file, with say 20 rows showing both your input ... and your expected result ...:)

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

  • Oops, sorry to be unclear. I realize you couldn't determine which areas of the spreadsheet I needed populated. In your first reply you said to, "Feel free to attach a sample file, with say 20 rows showing both your input ... and your expected result ...", and I did that, I just didn't tell you which one is which.


    Since your sample file you you sent back you removed part of the needed dataset, I'm reattaching the mockup I did with some color. We're trying to derive the monthly totals by state. The Output in this case is the green shaded areas. I typed in expected values here to illustrate expected output only. The input is obtained from the blue shaded area showing the client start month, as well as their monthly cost in years 1-5. Looking for a dynamic formula that can take into account which month and year (blue output) we're calculating, and sum up all the totals for each client, taking into account if that month is in year 1, year 2, etc.


    Does that help to clarify it? I wish it was as easy as having an output tab that summarized the data!

  • Thanks for the clarification ...:)


    Will take a look at your objective ;)

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

  • Could we agree on a couple of facts ...


    1. For the sake of allowing further expansion of your ' Green ' Output range .... the ' Purple ' Input range has be located on a separate-dedicated worksheet ....


    2. In your example, all figures appearing in the ' Green ' Output range .... are not related at all with the actual figures which should be retrieved for the ' Purple ' Input range

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

  • Based on the assumptions explained in the previous post ...


    Take a look at your attached test file ...


    Hope this will help

    :)

    Files

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

  • Hello again,


    Depending on the way you actually operate ... you could find it handy to use the Data Validation feature for your States ...:)


    see your attached test file ...

    Files

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

  • Hi Carim,


    I think you're still thinking that we need to summarize existing data from the purple data by migrating it to the green area in a more simple form than we are trying to achieve. I would like to keep the data on the same sheet if possible too- I don't expect to expand this sheet beyond 2025.


    In your latest example file, it seems that you're showing for TX, that January and February 2020 are both 1000 which is not correct. Since in January we are only counting Client A (because they started 01/01/2020), but in February we are counting Client B as well. They are both in their first year until January of 2021 where Client A rate increases to their Year 2 price. So Jan 2021 would be Client A Year 2 Rate + Client B Year 1 Rate. February 2021 Client B ages to Year 2, so Feb 2021 would be Client A Year 2 Rate + Client B Year 2 Rate.


    I added client names so we can discuss. Is that anymore clear?

  • I should add that a client is counted every month from their start date, not just in the month of their start date. So if a client is charged starting on 02/02/2020 then they will see their fee counted in March, April and so on.

  • For sure... I am missing your underlying logic ...


    Why don't you take full advantage of your test file ...


    by showing in your ' Green Output area ' how to come up with the amounts shown ... with formulas using cells from the ' Purple Area '


    instead of typing in numbers ... which does not help to decipher the logic you are using ...;)

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

  • Apparently the cumulative logic of year 2020 is fine ...


    But, if the same formula is applied ...there are differences for the following years ...

    Files

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

  • Feel free to comment on proposed formulas ...

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