Sumproduct

[tiredofit]

This is my data:

1   10
2   20
3   30
4   40
5   50

The formula:

=SUMPRODUCT((A1:A5<3)*B1:B5)

returns a value of 30.

I assume this is because it searches for all those values in column A where the value is < 3 (ie the 1 and the 2) and sums their correspondng values, ie 10 and 20.

However, if my formula was:

=SUMPRODUCT((A1:A5<3)+B1:B5)

I get a value of 152.

what is happening here?

Thanks

 

[shg]

Watch the formula evaluate.

 

[Special-K99]

I think it's multiplying A1 by A2 since they're both < 3
and it's adding all of column B (B1:B5)

+ is regarded as OR by SUMPRODUCT

Column B's total is 150.

 

[Jonmo1]

=SUMPRODUCT((A1:A5<3)*B1:B5)

(A1:A5<3) is an array of expressions, or Questions if you will.
Is A1 < 3, Is A2 <3, Is A3 < 3 etc..
Each with a true or false answer.

=SUMPRODUCT({TRUE,TRUE,FALSE,FALSE,FALSE}*B1:B5)

When a math operation like +-/* is performed on a true/false value, TRUE is treated as 1, FALSE is 0
=SUMPRODUCT({1,1,0,0,0}*B1:B5)
=SUMPRODUCT({1*B1,1*B2,0*B3,0*B4,0*B5})
=SUMPRODUCT({1*10,1*20,0*30,0*40,0*50})
=SUMPRODUCT({10,20,0,0,0})
30

=SUMPRODUCT((A1:A5<3)+B1:B5)
=SUMPRODUCT({TRUE,TRUE,FALSE,FALSE,FALSE}+B1:B5)
=SUMPRODUCT({1,1,0,0,0}+B1:B5)
=SUMPRODUCT({1+B1,1+B2,0+B3,0+B4,0+B5})
=SUMPRODUCT({1+10,1+20,0+30,0+40,0+50})
=SUMPRODUCT({11,21,30,40,50})
152

 

[tiredofit]

Thanks