| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of values
I have column A that contains many account numbers and i have column B which
is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of values
The command you need is SUMIF. I will set it to match the range in your
example but you will need to adjust the range to the max number of rows you will use. There are several ways to do this. But I am going to show you two. 1) Set up this way if you want to enter the account number you wish to sum and have the SUMIF formula pick read the account number you entered. This way, you can simply enter an account number you wish to check without having to change the formula. Enter the account number you wish to sum in cell C1. In any other cell you choose, say C3, type the following formula: =SUMIF(A1:A5,"="&C1,B1:B5) 2) This option means you have to change the search criteria each time you want to check a different account number. Again, choose any cell you wish to use. The formula is: =SUMIF(A1:A5,"=10023",B1:B5) An added feature, if you will, can be to count the number of transactions for the account you searched. I am going type this formula based on option 1 above. Type in the cell just below SUMIF the following: =COUNTIF(A1:A5,"="&C1,B1:B5) Hope that works for you. "jk" wrote: I have column A that contains many account numbers and i have column B which is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of values
Sorry. after reading your request more closely, I see you want to keep track
of ALL accounts. I am not sure how to PULL the accounts from your list without duplicating. If I figure it out, I will get back with you unless someone else shows you how. "jk" wrote: I have column A that contains many account numbers and i have column B which is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of values
C1: =A1
C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(C$1:C1,$A$1:$A$2 0&""),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Copy C2 down as far as you need D1: =SUMIF(A:A,C1,B:B) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jk" wrote in message ... I have column A that contains many account numbers and i have column B which is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of values
Accounts are duplicated in this column since a data entry person keys the
account for the customers purchase's.I want to be able to sum up any account regardless how many times it appears in the column. It is a way to have summed results for each account.Does that help to explain? "WLMPilot" wrote: Sorry. after reading your request more closely, I see you want to keep track of ALL accounts. I am not sure how to PULL the accounts from your list without duplicating. If I figure it out, I will get back with you unless someone else shows you how. "jk" wrote: I have column A that contains many account numbers and i have column B which is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of values
Two for sure dumb questions, why have a column "D" if coulmn "A" is for
accounts,column "B" is for corresponding dollar amounts and column "C" is for the results of sumed account values? Do you hit cntrl shift enter first on a cell then enter the formula? "Bob Phillips" wrote: C1: =A1 C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(C$1:C1,$A$1:$A$2 0&""),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Copy C2 down as far as you need D1: =SUMIF(A:A,C1,B:B) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jk" wrote in message ... I have column A that contains many account numbers and i have column B which is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of values
When you finish typing in the formula then hit Ctrl+Shift+Enter (not just
enter as you normally would). This makes the formula into an array formula. You will know if you did it right because in the formula bar the formula will have curly braces { } around it. -- HTH... Jim Thomlinson "jk" wrote: Two for sure dumb questions, why have a column "D" if coulmn "A" is for accounts,column "B" is for corresponding dollar amounts and column "C" is for the results of sumed account values? Do you hit cntrl shift enter first on a cell then enter the formula? "Bob Phillips" wrote: C1: =A1 C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(C$1:C1,$A$1:$A$2 0&""),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Copy C2 down as far as you need D1: =SUMIF(A:A,C1,B:B) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jk" wrote in message ... I have column A that contains many account numbers and i have column B which is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum of values
Thank you all,
I really apreciate your valuable knowledge, it works great! "Jim Thomlinson" wrote: When you finish typing in the formula then hit Ctrl+Shift+Enter (not just enter as you normally would). This makes the formula into an array formula. You will know if you did it right because in the formula bar the formula will have curly braces { } around it. -- HTH... Jim Thomlinson "jk" wrote: Two for sure dumb questions, why have a column "D" if coulmn "A" is for accounts,column "B" is for corresponding dollar amounts and column "C" is for the results of sumed account values? Do you hit cntrl shift enter first on a cell then enter the formula? "Bob Phillips" wrote: C1: =A1 C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(C$1:C1,$A$1:$A$2 0&""),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Copy C2 down as far as you need D1: =SUMIF(A:A,C1,B:B) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jk" wrote in message ... I have column A that contains many account numbers and i have column B which is a dollar value that corresponds to each account. A B 10023 $25.00 10024 $20.00 10025 $25.00 10023 $25.00 10026 $25.00 Is it possible to assign cells in column C to sum the amounts which equal the accounts. For example account 10023 sums up to $50.00 and i would like each time the account of 10023 is entered into column A ,it would accumulate the amount in a cell of column C .........can this be done? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 doesnt show Y-axis values when the values are small. | Charts and Charting in Excel | |||
| Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
| Search/Filter to find values in another range based on two cell values | Excel Programming | |||
| How do I search thr'o column and put unique values in differnt sheet and sum corresponding values in | Excel Programming | |||
| Predict Y-values on new X-values based on other actual X and Y values? | Excel Programming | |||
Linear Mode
