Excel Sumproduct Formula

[acool]

Hi Everyone,

I am currently trying to create a sum product formula which calculates the Number of Months to repay a purchase value of $4,833,00. I would normally do this by using the formula F1/Sumproduct(A2:A7,C2:C7). However, in this instance, each of the products have a specific number of months of units on hand. After that month is surpassed, I would no longer like that product to contribute to the contribution margin calculation. For example, on Product A, after 8 Months I would not those contribution margin dollars to be factored into my calculation. In this example the payback period should equal to 9.9 Months. I have already received some great advice from member of the form on approaching this using a goal seek, however, I would like to create a formula which populates the payback period in Cell F2. Any help would be greatly appreciated. Thank You!

 

[Dave Patton]

Did Stephen Crump's suggestion not work for you?
If not, what result did you expect?

T202312a.xlsm
	A	B	C	D	E	F
1					Amount	4,833,000.00
2	6300	8	23.15		Months	10
3	5200	9	5.55
4	4800	13	36.9
5	5500	14	6.65
6	2100	24	39.65
7	3250	25	14.9
6a

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(IF(B2:B7<C9,B2:B7,C9),A2:A7,C2:C7)
F2	F2	=XMATCH(F1,SCAN(0,BYCOL((B2:B7>=SEQUENCE(,MAX(B2:B7)))*(A2:A7*C2:C7),LAMBDA(c,SUM(c))),LAMBDA(a,b,a+b)),1)

 

[acool]

Did Stephen Crump's suggestion not work for you?
If not, what result did you expect?

T202312a.xlsm
	A	B	C	D	E	F
1					Amount	4,833,000.00
2	6300	8	23.15		Months	10
3	5200	9	5.55
4	4800	13	36.9
5	5500	14	6.65
6	2100	24	39.65
7	3250	25	14.9
6a

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(IF(B2:B7<C9,B2:B7,C9),A2:A7,C2:C7)
F2	F2	=XMATCH(F1,SCAN(0,BYCOL((B2:B7>=SEQUENCE(,MAX(B2:B7)))*(A2:A7*C2:C7),LAMBDA(c,SUM(c))),LAMBDA(a,b,a+b)),1)

@Dave Patton Just curious-What is the C9 Reference in this formula? But this is exactly what I am looking for! Thank you Both

 

[Dave Patton]

I was side tracked. I did not intend to post the formula.
What result did you expect?
What is the expected calculation if 8 is the limiting factor for one line?
Why did you start a new thread?
Please check your previous thread.