XL 2003 & Sumproduct Question

K

Kevin H. Stecyk

Hi,

As a subset of my problem, I am trying to figure out what is wrong my
sumproduct formula. I am sure that it is something simple.

I have the following cells:
A1=8; B1=16
A2:B3 = 0

In cell A5, I want the sum of Col A when Col A is less than Col B.

I have...

=sumproduct(a1:a3<b1:b3, a1:a3)

When I use this equation, I get 0. I was expecting 8.

When I select and evaluate A1:A3<B1:B3, I get {true; false; false}. When I
select and evaluate a1:a3, I get {8;0;0}

What am I doing wrong that I am not getting 8 for my answer?

Thank you.

Kevin
 
P

Peo Sjoblom

Kevin,

try

=SUMPRODUCT(--(A1:A3<B1:B3), A1:A3)

or

=SUMPRODUCT((A1:A3<B1:B3)*(A1:A3))


Regards,

Peo Sjoblom
 
K

Kevin H. Stecyk

Peo Sjoblom wrote....
Kevin,

try

=SUMPRODUCT(--(A1:A3<B1:B3), A1:A3)

or

=SUMPRODUCT((A1:A3<B1:B3)*(A1:A3))
Click to expand...


Yes, both work. Thank you.

If it isn't too much trouble, can you please remind me why the double
negative in the sum product is necessary? Or point me to where this is
explained.

Again, thank you very much!

Best regards,
Kevin
 
Top