Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Individual Account Balances
I have been working on this all morning and I can't seem to get it...
I want to get a single total for each account number when my data has a random distribution of accounts for example: Account Balance Total Owed (what I want/can't figure out) A100 1 1 A101 2 A101 1 3 A102 3 3 A103 2 2 A104 2 2 A105 3 A105 3 A105 4 10 I haven't been able to combine a logic function with a nested sum to make this happen, Any ideas? Brendan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Individual Account Balances
This would be a perfect situation for a pivot table.
Highlight all your data... click on Data--Pivot Table from the menu bar There just follow the instructions. Put your accounts in the row heading and the total owed in the data section... and magically it creates what you want. "Brendan Hanrahan" wrote: I have been working on this all morning and I can't seem to get it... I want to get a single total for each account number when my data has a random distribution of accounts for example: Account Balance Total Owed (what I want/can't figure out) A100 1 1 A101 2 A101 1 3 A102 3 3 A103 2 2 A104 2 2 A105 3 A105 3 A105 4 10 I haven't been able to combine a logic function with a nested sum to make this happen, Any ideas? Brendan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Individual Account Balances
A couple of possible ways to go about this. As formulas it is a bit of an
uphill battle. My recommended solution would be either a pivot table or inserting subtotals. Data - Subtotal (fairly self explanatory) Data - Pivot Table or Pivot Chart (Follow the wizard) -- HTH... Jim Thomlinson "Brendan Hanrahan" wrote: I have been working on this all morning and I can't seem to get it... I want to get a single total for each account number when my data has a random distribution of accounts for example: Account Balance Total Owed (what I want/can't figure out) A100 1 1 A101 2 A101 1 3 A102 3 3 A103 2 2 A104 2 2 A105 3 A105 3 A105 4 10 I haven't been able to combine a logic function with a nested sum to make this happen, Any ideas? Brendan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Individual Account Balances
As a side comment to Jim Thomlinson's sub-total suggestion, if that's the
route you choose just make sure that the data has been sorted by account number before creating the sub-totals. -- Kevin Backmann "Jim Thomlinson" wrote: A couple of possible ways to go about this. As formulas it is a bit of an uphill battle. My recommended solution would be either a pivot table or inserting subtotals. Data - Subtotal (fairly self explanatory) Data - Pivot Table or Pivot Chart (Follow the wizard) -- HTH... Jim Thomlinson "Brendan Hanrahan" wrote: I have been working on this all morning and I can't seem to get it... I want to get a single total for each account number when my data has a random distribution of accounts for example: Account Balance Total Owed (what I want/can't figure out) A100 1 1 A101 2 A101 1 3 A102 3 3 A103 2 2 A104 2 2 A105 3 A105 3 A105 4 10 I haven't been able to combine a logic function with a nested sum to make this happen, Any ideas? Brendan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Individual Account Balances
With the Account number in A1:A9 and the Balances in B1:B9, in C1
=SUMPRODUCT(($A$1:$A$9=A1)*($B$1:$B$9)) and drag down to C9 Regards, Alan. "Brendan Hanrahan" wrote in message ... I have been working on this all morning and I can't seem to get it... I want to get a single total for each account number when my data has a random distribution of accounts for example: Account Balance Total Owed (what I want/can't figure out) A100 1 1 A101 2 A101 1 3 A102 3 3 A103 2 2 A104 2 2 A105 3 A105 3 A105 4 10 I haven't been able to combine a logic function with a nested sum to make this happen, Any ideas? Brendan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a service account be embedded in Workbook so that by default the macros are run with service account credentials and not the user credentials??? | Excel Discussion (Misc queries) | |||
Running Balances | Excel Discussion (Misc queries) | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
How to link endings balances to beginning balances on different sh | Excel Worksheet Functions |