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.