XL 2003 & Sumproduct Question

[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

 

[Guest]

Kevin,

try

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

or

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


Regards,

Peo Sjoblom

 

[Kevin H. Stecyk]

Peo Sjoblom wrote....

Kevin,

try

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

or

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

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

 

[Guest]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

another thing is that if by some strange coincidence should have a text
value in the column that is summed you won't get a value error like you would
if you multiply the arrays with each other


Peo

 

[Kevin H. Stecyk]

Peo Sjoblom wrote...

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

another thing is that if by some strange coincidence should have a text
value in the column that is summed you won't get a value error like you
would
if you multiply the arrays with each other

Thank you very much!!

Best regards,
Kevin