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

"Kevin H. Stecyk" wrote:

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

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

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" wrote:

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

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