Sumproduct Combined with Min function

[Mooping]

Hi all, I have two columns as follows

A B
40 0.5
35 0.2
60 1.25
70 2.00

And would like to Sumproduct the two but constraining the maximum value in column B to 1. Having a separate column that picks up the Min(B,1) is not practical as in practice I have a large number of B type columns.

My first attempt at a solution was
=+SUMPRODUCT($E9:$E1390,(MIN(D9:D1390,1)))
but it returns an error.

Many thanks for your help/suggestions!!

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(A1:A4,B1:B4,--(B1:B4<=1))

Hi all, I have two columns as follows

A B
40 0.5
35 0.2
60 1.25
70 2.00

And would like to Sumproduct the two but constraining the maximum value in column B to 1. Having a separate column that picks up the Min(B,1) is not practical as in practice I have a large number of B type columns.

My first attempt at a solution was
=+SUMPRODUCT($E9:$E1390,(MIN(D9:D1390,1)))
but it returns an error.

Many thanks for your help/suggestions!!

 

[lenze]

Hi and welcome to the board!!

=SUM(IF(B1:B4<=1,A1:A4))

This is an array formula, so you must confirm it by using CTRL+SHIFT+ENTER

lenze

 

[Mooping]

Thanks for the warm welcome and quick suggestions - it strikes me that the proposed answers will only sumproduct if the value in column B is less than 1 and ignores values of column B that are greater than 1 -I'm looking for a solution that takes the value in column B if this is less than 1 and uses 1 if it's greater than 1. Any thoughts?

Thank you very very much!

 

[Mooping]

Actually, no, you are right. This seems to work like a charm.

Thanks a lot!

Hi and welcome to the board!!

=SUM(IF(B1:B4<=1,A1:A4))

This is an array formula, so you must confirm it by using CTRL+SHIFT+ENTER

lenze

 

[Aladin Akyurek]

Actually, no, you are right. This seems to work like a charm.

Thanks a lot!

If so, a simpler and afster formula would be:

=SUMIF(B1:B4,"<=1",A1:A4)