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.

Quote from Carim

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

Hi Carim,

Thanks for the feedback. I've mocked up a sheet, hopefully it's clear what I'm trying to do. I want totals by state taking into account annual increases.

Thank you!

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!

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.

Hi Carim,

So I've tried to provide the totals in narrative form, I've given examples in numeric form, I'm not sure how else to communicate it. Thank you for trying.