sumproduct? sumif(sumproduct)?
 

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?
--
David

sumproduct? sumif(sumproduct)?
 

David,

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

HTH,
Bernie
MS Excel MVP


"David" wrote in message
...
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?
--
David

sumproduct? sumif(sumproduct)?
 

I think this is what you're after:

For the total positive change:

=SUMPRODUCT(--(B1:B4A1: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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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?
--
David

sumproduct? sumif(sumproduct)?
 

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


"Bernie Deitrick" wrote:

David,

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

HTH,
Bernie
MS Excel MVP


"David" wrote in message
...
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?
--
David