sumproduct? sumif(sumproduct)?

[Guest]

Hi Group,

I am tring to come up with a formula. I have two columns of numbers ie. the
"change" column is only for reference and illistration.
ColA ColB Change
70.21 72.2 1.99
30.35 30.0 -.35
20 21 1
19 18 -1

The actual is a much longer list. I am trying to find the sum of two
differances, for only the positive changes and only the negative changes. The
"change" column does not really exist nor do I want to add it to the sheet,
but I am looking for a formula that will these differences. I have tried
several with out success.

Any ideas?

 

[Bernie Deitrick]

David,

=SUMPRODUCT((B1:B100>A1:A100)*(B1:B100-A1:A100))
=SUMPRODUCT((B1:B100<A1:A100)*(B1:B100-A1:A100))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

I think this is what you're after:

For the total positive change:

=SUMPRODUCT(--(B1:B4>A1:A4),B1:B4-A1:A4)

Based on your sample returns 2.99

For the total negative change:

=SUMPRODUCT(--(B1:B4<A1:A4),B1:B4-A1:A4)

Based on your sample returns -1.35

 

[Guest]

Thank you Bernie. This did exactly what I was looking for.