looking for a possible simpler solution (formula) to calculate ~cashflow from customers

hello everyone.
thanks for looking into my post!

not sure how to exactly describe the solution I'm looking for - the case is better visible in the excel attached.

basically, I have new customers coming in each month but their revenues are pre-defined depending on the month they are in.
so, a number of new customers acquired in a month 2 will have their revenue/customer at month 1 as it the first month for them.

so now I'm just cross-multiplying the cells, but while it's ok on a small scale, it's kinda troublesome manual work with dozens of months.

thank you again for any tips and have a nice day!

-----------------------------------------------

so, as I tried to describe, I'm trying to calculate e.g. revenue by month generated by all customers.
I have two inputs - # of new customers each month and revenue that a customer brings for each his/her month number.

as shown in the example file, the total revenue for month 3 will consist of:
- new customers of month 1 * revenue per customer of month 3
- new customers of month 2 * revenue per customer of month 2 (because 3rd month overall is only 2nd month for those customers)
- new customers of month 3 * revenue per customer of month 1 (because its only 1st month for them)

so now there are simple straightforward ~cross-multiplying formulas reflecting above
but given smth like 36 months, each having different # of customers and different revenue per customer level each month, the last cell's formula is just a lot (36) of 'manual' multiplication formulas.

so my hope is that maybe someone already tackled this with some sum/array/idk-approach to simplify that formula.

I extended the example to reflect the pain point - the let me know if this one is clearer. thanks again!

neveum,

Attached does what you want.

Cols A - C list the clients, their starting dates and their monthly revenues (assuming whatever sum is in Col C does not change over the months)

Formula in F2 and dragged across to Q2 counts the number of active clients each month as follows:

=COUNTIFS($B$2:$B$14,">="&$F$1,$B$2:$B$14,"<="&EOMONTH(F1,0))

(Count number of records in Col B where date is after start of Jan and before or on the end of (whichever month is in Row 1 of that column).

Formula in F3 and dragged across to Q2 totals the revenue earned from active clients each month as follows:

=SUMIFS($C$2:$C$14,$B$2:$B$14,">="&$F$1,$B$2:$B$14,"<="&EOMONTH(F$1,0))

(Total the values in Col C where the date in Col B is between start of Jan and end of (whichever month is in Row 1 of that column)

Hope this helps

Ochimus