Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing values... conditionally
I have two columns: Column A for a series of account numbers for each asset
comprising it; the second, Column B holding dollar values. In each column, each account is separated from the next by a row where Column A has "Sub Account Number" and Column B has "Market Value". How do I sum the values in Column B for each unique Sub Account Number and place the result in Column C adjacent to each account's last Column B entry? I have typed in "total" where I need the summed values to appear. Here is how it looks: Sub Account Number Market Value 10534161 $ - 10534161 $ - 10534161 $ 288.44 10534161 $ 14,463.87 10534161 $ 2,007.25 10534161 $ 13,001.17 total Sub Account Number Market Value 10610506 $ 119,892.62 10610506 $ - 10610506 $ - 10610506 $ 8,523.41 10610506 $ 137,554.40 10610506 $ 179,931.31 10610506 $ 22,761.19 10610506 $ 22,940.00 10610506 $ 222,170.79 10610506 $ 1,255,596.00 total Sub Account Number Market Value 10827866 $ 212.27 10827866 $ - 10827866 $ - 10827866 $ 6,373.75 10827866 $ 22,929.03 10827866 $ 28,844.96 total Sub Account Number Market Value 10829866 $ 417.08 10829866 $ - 10829866 $ - 10829866 $ 22,929.03 10829866 $ 28,844.96 total Sub Account Number Market Value 10854398 $ 1,185.40 10854398 $ - 10854398 $ - 10854398 $ 9,934.68 10854398 $ 4,826.20 10854398 $ 4,464.85 10854398 $ 4,301.25 10854398 $ 5,595.59 10854398 $ 14,868.90 total My thanks in advance for any suggestions. Brad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing values... conditionally
Type this in C1 and then drag it down:
=IF(A2="Sub Account Number",SUMIF(B:B,A1,A:A),"") "Brad" wrote: I have two columns: Column A for a series of account numbers for each asset comprising it; the second, Column B holding dollar values. In each column, each account is separated from the next by a row where Column A has "Sub Account Number" and Column B has "Market Value". How do I sum the values in Column B for each unique Sub Account Number and place the result in Column C adjacent to each account's last Column B entry? I have typed in "total" where I need the summed values to appear. Here is how it looks: Sub Account Number Market Value 10534161 $ - 10534161 $ - 10534161 $ 288.44 10534161 $ 14,463.87 10534161 $ 2,007.25 10534161 $ 13,001.17 total Sub Account Number Market Value 10610506 $ 119,892.62 10610506 $ - 10610506 $ - 10610506 $ 8,523.41 10610506 $ 137,554.40 10610506 $ 179,931.31 10610506 $ 22,761.19 10610506 $ 22,940.00 10610506 $ 222,170.79 10610506 $ 1,255,596.00 total Sub Account Number Market Value 10827866 $ 212.27 10827866 $ - 10827866 $ - 10827866 $ 6,373.75 10827866 $ 22,929.03 10827866 $ 28,844.96 total Sub Account Number Market Value 10829866 $ 417.08 10829866 $ - 10829866 $ - 10829866 $ 22,929.03 10829866 $ 28,844.96 total Sub Account Number Market Value 10854398 $ 1,185.40 10854398 $ - 10854398 $ - 10854398 $ 9,934.68 10854398 $ 4,826.20 10854398 $ 4,464.85 10854398 $ 4,301.25 10854398 $ 5,595.59 10854398 $ 14,868.90 total My thanks in advance for any suggestions. Brad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing values... conditionally
Back to front... use this.
=IF(A2="Sub Account Number",SUMIF(A:A,A1,B:BA),"") "Sam Wilson" wrote: Type this in C1 and then drag it down: =IF(A2="Sub Account Number",SUMIF(B:B,A1,A:A),"") "Brad" wrote: I have two columns: Column A for a series of account numbers for each asset comprising it; the second, Column B holding dollar values. In each column, each account is separated from the next by a row where Column A has "Sub Account Number" and Column B has "Market Value". How do I sum the values in Column B for each unique Sub Account Number and place the result in Column C adjacent to each account's last Column B entry? I have typed in "total" where I need the summed values to appear. Here is how it looks: Sub Account Number Market Value 10534161 $ - 10534161 $ - 10534161 $ 288.44 10534161 $ 14,463.87 10534161 $ 2,007.25 10534161 $ 13,001.17 total Sub Account Number Market Value 10610506 $ 119,892.62 10610506 $ - 10610506 $ - 10610506 $ 8,523.41 10610506 $ 137,554.40 10610506 $ 179,931.31 10610506 $ 22,761.19 10610506 $ 22,940.00 10610506 $ 222,170.79 10610506 $ 1,255,596.00 total Sub Account Number Market Value 10827866 $ 212.27 10827866 $ - 10827866 $ - 10827866 $ 6,373.75 10827866 $ 22,929.03 10827866 $ 28,844.96 total Sub Account Number Market Value 10829866 $ 417.08 10829866 $ - 10829866 $ - 10829866 $ 22,929.03 10829866 $ 28,844.96 total Sub Account Number Market Value 10854398 $ 1,185.40 10854398 $ - 10854398 $ - 10854398 $ 9,934.68 10854398 $ 4,826.20 10854398 $ 4,464.85 10854398 $ 4,301.25 10854398 $ 5,595.59 10854398 $ 14,868.90 total My thanks in advance for any suggestions. Brad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing values... conditionally
Great! Thanks Sam...
Brad "Sam Wilson" wrote in message ... Back to front... use this. =IF(A2="Sub Account Number",SUMIF(A:A,A1,B:BA),"") "Sam Wilson" wrote: Type this in C1 and then drag it down: =IF(A2="Sub Account Number",SUMIF(B:B,A1,A:A),"") "Brad" wrote: I have two columns: Column A for a series of account numbers for each asset comprising it; the second, Column B holding dollar values. In each column, each account is separated from the next by a row where Column A has "Sub Account Number" and Column B has "Market Value". How do I sum the values in Column B for each unique Sub Account Number and place the result in Column C adjacent to each account's last Column B entry? I have typed in "total" where I need the summed values to appear. Here is how it looks: Sub Account Number Market Value 10534161 $ - 10534161 $ - 10534161 $ 288.44 10534161 $ 14,463.87 10534161 $ 2,007.25 10534161 $ 13,001.17 total Sub Account Number Market Value 10610506 $ 119,892.62 10610506 $ - 10610506 $ - 10610506 $ 8,523.41 10610506 $ 137,554.40 10610506 $ 179,931.31 10610506 $ 22,761.19 10610506 $ 22,940.00 10610506 $ 222,170.79 10610506 $ 1,255,596.00 total Sub Account Number Market Value 10827866 $ 212.27 10827866 $ - 10827866 $ - 10827866 $ 6,373.75 10827866 $ 22,929.03 10827866 $ 28,844.96 total Sub Account Number Market Value 10829866 $ 417.08 10829866 $ - 10829866 $ - 10829866 $ 22,929.03 10829866 $ 28,844.96 total Sub Account Number Market Value 10854398 $ 1,185.40 10854398 $ - 10854398 $ - 10854398 $ 9,934.68 10854398 $ 4,826.20 10854398 $ 4,464.85 10854398 $ 4,301.25 10854398 $ 5,595.59 10854398 $ 14,868.90 total My thanks in advance for any suggestions. Brad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing across worksheets conditionally and being able to copy for | Excel Discussion (Misc queries) | |||
Conditionally summing data from two columns depending on their value. | Excel Worksheet Functions | |||
counting unique values conditionally | Excel Discussion (Misc queries) | |||
Conditionally summing cells based on conditions in other rows | Excel Worksheet Functions | |||
How to conditionally sum values depending on values in other colums | Excel Programming |