Hi there,
I'm having a problem with someting I need to do. I need to SUM up diferences
with a criteria!! For example:
A B
1 1.544 -322 (A2-A1)
2 1.222 0 (A3-A2)
3 1.222 541 (A4-A5)
4 1.763 37 (A5-A4)
5 1.800 -563 ..........

Total needed = is the sum of positive differences 541+37 = 578
I would like to know if there is a formula which gives me this result (578)
without using the collumn B ??

Can you help me? thanks.
Brgds
Nuno

Try the following formula:

=SUM(IF((B3:B7)-(B2:B6)0,(B3:B7)-(B2:B6),0))

You have to enter it as an array: press Press CTRL+SHIFT+ENTER when entering
the formula instead of ENTER. It will look like this if you entered it
correctly:

={ SUM(IF((B3:B7)-(B2:B6)0,(B3:B7)-(B2:B6),0))}


"Nuno" wrote:

Hi there,
I'm having a problem with someting I need to do. I need to SUM up diferences
with a criteria!! For example:
A B
1 1.544 -322 (A2-A1)
2 1.222 0 (A3-A2)
3 1.222 541 (A4-A5)
4 1.763 37 (A5-A4)
5 1.800 -563 ..........

Total needed = is the sum of positive differences 541+37 = 578
I would like to know if there is a formula which gives me this result (578)
without using the collumn B ??

Can you help me? thanks.
Brgds
Nuno

Hi,

I don't understand. Summing numbers in a range that are greater than zero
can be done with:-

=SUMIF(B1:B5,"0",B1:B5)

But in this case you don't want to use column B so how are the numbers in
column B arrived at.

Mike

"Nuno" wrote:

Hi there,
I'm having a problem with someting I need to do. I need to SUM up diferences
with a criteria!! For example:
A B
1 1.544 -322 (A2-A1)
2 1.222 0 (A3-A2)
3 1.222 541 (A4-A5)
4 1.763 37 (A5-A4)
5 1.800 -563 ..........

Total needed = is the sum of positive differences 541+37 = 578
I would like to know if there is a formula which gives me this result (578)
without using the collumn B ??

Can you help me? thanks.
Brgds
Nuno

Sorry, I had posted your data in my "B" column...

For your example, the right formula would be:

=SUM(IF((A3:A7)-(A2:A6)0,(A3:A7)-(A2:A6),0))

Sorry for the confusion!


"Idoia" wrote:

Try the following formula:

=SUM(IF((B3:B7)-(B2:B6)0,(B3:B7)-(B2:B6),0))

You have to enter it as an array: press Press CTRL+SHIFT+ENTER when entering
the formula instead of ENTER. It will look like this if you entered it
correctly:

={ SUM(IF((B3:B7)-(B2:B6)0,(B3:B7)-(B2:B6),0))}


"Nuno" wrote:

Hi there,
I'm having a problem with someting I need to do. I need to SUM up diferences
with a criteria!! For example:
A B
1 1.544 -322 (A2-A1)
2 1.222 0 (A3-A2)
3 1.222 541 (A4-A5)
4 1.763 37 (A5-A4)
5 1.800 -563 ..........

Total needed = is the sum of positive differences 541+37 = 578
I would like to know if there is a formula which gives me this result (578)
without using the collumn B ??

Can you help me? thanks.
Brgds
Nuno

Another way ..

Try in say, B1:
=SUMPRODUCT(--(A2:A5-A1:A40),A2:A5-A1:A4)*1000
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nuno" wrote:
Hi there,
I'm having a problem with someting I need to do. I need to SUM up diferences
with a criteria!! For example:
A B
1 1.544 -322 (A2-A1)
2 1.222 0 (A3-A2)
3 1.222 541 (A4-A5)
4 1.763 37 (A5-A4)
5 1.800 -563 ..........

Total needed = is the sum of positive differences 541+37 = 578
I would like to know if there is a formula which gives me this result (578)
without using the collumn B ??

Can you help me? thanks.
Brgds
Nuno

Thanks Idoia and Max, it works!

"Idoia" wrote:

Sorry, I had posted your data in my "B" column...

For your example, the right formula would be:

=SUM(IF((A3:A7)-(A2:A6)0,(A3:A7)-(A2:A6),0))

Sorry for the confusion!


"Idoia" wrote:

Try the following formula:

=SUM(IF((B3:B7)-(B2:B6)0,(B3:B7)-(B2:B6),0))

You have to enter it as an array: press Press CTRL+SHIFT+ENTER when entering
the formula instead of ENTER. It will look like this if you entered it
correctly:

={ SUM(IF((B3:B7)-(B2:B6)0,(B3:B7)-(B2:B6),0))}


"Nuno" wrote:

Hi there,
I'm having a problem with someting I need to do. I need to SUM up diferences
with a criteria!! For example:
A B
1 1.544 -322 (A2-A1)
2 1.222 0 (A3-A2)
3 1.222 541 (A4-A5)
4 1.763 37 (A5-A4)
5 1.800 -563 ..........

Total needed = is the sum of positive differences 541+37 = 578
I would like to know if there is a formula which gives me this result (578)
without using the collumn B ??

Can you help me? thanks.
Brgds
Nuno

I love that approach, Thanks!!! I had been wondering how to transform TRUE
and FALSE into 1 and 0 for some time now. I'm sure this will speed up my
calculation time, with arrays it does get slow very quickly.

"Max" wrote:

Another way ..

Try in say, B1:
=SUMPRODUCT(--(A2:A5-A1:A40),A2:A5-A1:A4)*1000
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nuno" wrote:
Hi there,
I'm having a problem with someting I need to do. I need to SUM up diferences
with a criteria!! For example:
A B
1 1.544 -322 (A2-A1)
2 1.222 0 (A3-A2)
3 1.222 541 (A4-A5)
4 1.763 37 (A5-A4)
5 1.800 -563 ..........

Total needed = is the sum of positive differences 541+37 = 578
I would like to know if there is a formula which gives me this result (578)
without using the collumn B ??

Can you help me? thanks.
Brgds
Nuno