Is there a way to use the offset function in Excel to sum the contribution from multiple cohorts ove-CodePudding

I am trying to find a formula that will generate the total profit for a number of cohorts that generate a different periodic profit per unit, without having to create a line item for each cohort.

In this example, the profit contributed by each widget over time is shown in row 3, and the number of widgets issued in each cohort is shown vertically in column B. Each unit will contribute $25 in the first period, $60 in the second period, and so on. So year 1 total profit would be 100 x $25 = $2,500. Then in year 2, the Y1 cohort would generate 100 x $60 and the Y2 cohort would generate 200 x $25 for a total year 2 profit of $11,000.

Does someone know of a method in Excel that would work to consolidate the total profit calculation each year into a single formula? I am trying to model multiple line items over many periods, so looking for a more efficient solution.

Edit: In case this helps clarify the question, below is an image showing an example of another inefficient way to solve the problem in one line for year 4 total profits, but this is still not scalable. Also shown in text below.

`Year 4 total profit = 
Y1 units issued x P4 profit per unit  
Y2 units issued x P3 profit per unit  
Y3 units issued x P2 profit per unit  
Y4 units issued x P1 profit per unit`

It could be done a bit more easily in Excel 365 using Sequence() instead of row() and column(), but the same principle - generate a 2D matrix by comparing row and column numbers, then obtain its column totals using a standard method with Mmult.

I've filled in the intermediate results in C7:K15, but you only need the formula in C17.

CodePudding user response：

Office 365, in C17:

=SUM(INDEX($B7:$B15,SEQUENCE(COUNT($C3:C3)))*INDEX($C3:C3,SEQUENCE(COUNT($C3:C3),,COUNT($C3:C3),-1)))

and copied right.