XL 2003 & Sumproduct Question

  • Thread starter Thread starter Kevin H. Stecyk
  • Start date Start date
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
 
Kevin,

try

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

or

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


Regards,

Peo Sjoblom
 
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
 
Back
Top