SUMPRODUCT of MAX

[VanillaBean]

Book1
	A	B	C	D	E	F	G	H	I	J
1				0	1	2	3	4	5	6
2	1	0.5	300	300	300	300	450	600	750	900
3	0	0.25	200	0	50	100	150	200	250	300
4	0	1	100	0	100	200	300	400	500	600
5	1	0.25	600	600	600	600	600	600	750	900
6	1	0.3	100	100	100	100	100	120	150	180
7				1000	1150	1300	1600	1920	2400	2880
8
9	0	1300
10	1
11	2
12	3
13	4
14	5
Sheet1

Cell Formulas
Range	Formula
D2:J6	D2	=SUM(MAX($A2, ($B2*D$1))*$C2)
D7:J7	D7	=SUM(D2:D6)
B9	B9	=SUM(MAX($A$2:$A$6, ($B$2:$B$6*$A9))*$C$2:$C$6)

I need a formula that goes directly from cells A2:C6, to formulas in B9:B14. Note that A9:A14 correspond to the numbers in D1:J1. The results I want to generate in B9:B14 are in D7:J7.

Essentially, for each row in a range I want MAX of either col B or another cell multiplied by col A, and then multiply this col C. Then the SUM of this output across each row in the range.

 

[StephenCrump]

Welcome to the Forum!

B9: =SUM(IF(A$2:A$6>B$2:B$6*A9,A$2:A$6,B$2:B$6*A9)*C$2:C$6)