|
SUM up diferences with a criteria
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 |
SUM up diferences with a criteria
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 |
SUM up diferences with a criteria
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 |
SUM up diferences with a criteria
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 |
SUM up diferences with a criteria
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 |
SUM up diferences with a criteria
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 |
SUM up diferences with a criteria
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 |
| All times are GMT +1. The time now is 04:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com