Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
Hi! I have a long list with bank names and depost values. The list of banks
is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
Arne,
in C2 use the following formula and copy down: =IF(A3<A2,SUMIF(A:A,A2,B:B),"") HTH Kostis Vezerides Arne Hegefors wrote: Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
Hi Arne
One way In cell C1 =IF(A2=A1,"",SUMIF(A:A,A1,B:B)) copy down as far as required -- Regards Roger Govier "Arne Hegefors" wrote in message ... Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
thanks! that works fine except for the case where there is a bank with only
one occurence. Any thoughts on this is appreciated! "vezerid" skrev: Arne, in C2 use the following formula and copy down: =IF(A3<A2,SUMIF(A:A,A2,B:B),"") HTH Kostis Vezerides Arne Hegefors wrote: Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
Hi Arne
Works fine for me with single occurrence of a bank. I added to your data, Abbey 150 and Barclays 200 and in column C I get Null, Null, 274, Null, 460, 150, 200 -- Regards Roger Govier "Arne Hegefors" wrote in message ... thanks! that works fine except for the case where there is a bank with only one occurence. Any thoughts on this is appreciated! "vezerid" skrev: Arne, in C2 use the following formula and copy down: =IF(A3<A2,SUMIF(A:A,A2,B:B),"") HTH Kostis Vezerides Arne Hegefors wrote: Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
Hi! Yes i know that it works when you have the data like that but if you have
eg citi 100 citi 100 citi 100 rabo 21 abn 50 abn 75 abn 50 then you get in c: null, null, 300, 300, null, null, 175....Pls note the second 300, there is the problem but I have know idea how to solve it. pls help me!! "Roger Govier" skrev: Hi Arne Works fine for me with single occurrence of a bank. I added to your data, Abbey 150 and Barclays 200 and in column C I get Null, Null, 274, Null, 460, 150, 200 -- Regards Roger Govier "Arne Hegefors" wrote in message ... thanks! that works fine except for the case where there is a bank with only one occurence. Any thoughts on this is appreciated! "vezerid" skrev: Arne, in C2 use the following formula and copy down: =IF(A3<A2,SUMIF(A:A,A2,B:B),"") HTH Kostis Vezerides Arne Hegefors wrote: Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
Hi Arne
With that same data in A1:B7 with =IF(A2=A1,"",SUMIF(A:A,A1,B:B)) in cell C1 and copied down, I get Null, Null, 300, 21, Null, 175 -- Regards Roger Govier "Arne Hegefors" wrote in message ... Hi! Yes i know that it works when you have the data like that but if you have eg citi 100 citi 100 citi 100 rabo 21 abn 50 abn 75 abn 50 then you get in c: null, null, 300, 300, null, null, 175....Pls note the second 300, there is the problem but I have know idea how to solve it. pls help me!! "Roger Govier" skrev: Hi Arne Works fine for me with single occurrence of a bank. I added to your data, Abbey 150 and Barclays 200 and in column C I get Null, Null, 274, Null, 460, 150, 200 -- Regards Roger Govier "Arne Hegefors" wrote in message ... thanks! that works fine except for the case where there is a bank with only one occurence. Any thoughts on this is appreciated! "vezerid" skrev: Arne, in C2 use the following formula and copy down: =IF(A3<A2,SUMIF(A:A,A2,B:B),"") HTH Kostis Vezerides Arne Hegefors wrote: Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
yes thanks! my mistake! thnx alot!!!
"Roger Govier" skrev: Hi Arne One way In cell C1 =IF(A2=A1,"",SUMIF(A:A,A1,B:B)) copy down as far as required -- Regards Roger Govier "Arne Hegefors" wrote in message ... Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum in list
Hi Arne
Glad you got it sorted in the end. -- Regards Roger Govier "Arne Hegefors" wrote in message ... yes thanks! my mistake! thnx alot!!! "Roger Govier" skrev: Hi Arne One way In cell C1 =IF(A2=A1,"",SUMIF(A:A,A1,B:B)) copy down as far as required -- Regards Roger Govier "Arne Hegefors" wrote in message ... Hi! I have a long list with bank names and depost values. The list of banks is sorted based on bank names. One bank can occur several times in the list (but since it is sorted they come after each other). I want to have the sum of deposit values for every bank. How can this be done? Example: Cititgroup 120 Cititgroup 100 Cititgroup 54 ABN 320 ABN 140 etc. I want to have a third column with the sums like this: Cititgroup 120 Cititgroup 100 Cititgroup 54 274 ABN 320 ABN 140 460 etc. Any help very much appreciated! Thanks alot! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List for AutoFilter and list for Subtotals have different meanings? | New Users to Excel | |||
Filter the results of a list based on a previous vlookup against the same list | Excel Worksheet Functions | |||
Creating a List based on your choice from Another List | Excel Discussion (Misc queries) | |||
How to link data from one drop-down list to another | Excel Worksheet Functions | |||
Refresh a Validation List? | Excel Discussion (Misc queries) |