Sumproduct where minimum value applies

[Robert Davidson]

I need to apply a minimum price to a sumproduct formula. If I multiply Qty x Price and the amount is less than £100 then a minimum price of £100 applies.

I need this to be a solution with a sumproduct formula as I need to perform other calculations in the same cell with the result. Any help on this would be much appreciated.,

Qty	Price	Total	Comments
90	£5	£450	Total is >£100 so Qty x Price applies
5	£2	£100	5 x £2 is less than £100 so £100 applies
3	£12	£100	3 x £12 is less than £100 so £100 applies

 

[AlanY]

try =MAX(A2*B2,100)

 

[Robert Davidson]

try =MAX(A2*B2,100)

Hi Alan,

Yes that works for each row individually but I need to use sumproduct to give me the total answer in one cell without a helper column.

 

[AlanY]

Hi Alan,

Yes that works for each row individually but I need to use sumproduct to give me the total answer in one cell without a helper column.

ok, try this

Book1
	A	B
1	Qty	Price
2	90	£5
3	5	£2
4	3	£12
5
6	Total	650
Sheet1

Cell Formulas
Range	Formula
B6	B6	=SUMPRODUCT(--((A2:A4)*(B2:B4)>100),((A2:A4)*(B2:B4)))+SUMPRODUCT(--((A2:A4)*(B2:B4)<=100))*100

 

[Robert Davidson]

ok, try this

Book1
	A	B
1	Qty	Price
2	90	£5
3	5	£2
4	3	£12
5
6	Total	650
Sheet1

Cell Formulas
Range	Formula
B6	B6	=SUMPRODUCT(--((A2:A4)*(B2:B4)>100),((A2:A4)*(B2:B4)))+SUMPRODUCT(--((A2:A4)*(B2:B4)<=100))*100

Perfect. Many thanks Alan.

 

[AlanY]

Perfect. Many thanks Alan.

you're welcome

 

[Dave Patton]

An alternative

T202402a.xlsm
	A	B
1	Qty	Price
2	90	5
3	5	2
4	3	12
5
6	Total	650
7
3e

Cell Formulas
Range	Formula
B6	B6	=SUM(LET(a,((A2:A4)*(B2:B4)>100),IF(a,(A2:A4)*(B2:B4),100)))

 

[Dave Patton]

T202402a.xlsm
	A	B
1	Qty	Price
2	90	5
3	5	2
4	3	12
5
6	Total	650
7		650
3e

Cell Formulas
Range	Formula
B6	B6	=SUM(LET(a,((A2:A4)*(B2:B4)>100),IF(a,(A2:A4)*(B2:B4),100)))
B7	B7	=SUM(IF(A2:A4*B2:B4>100,A2:A4*B2:B4,100))