Sum Individual Account Balances

[Brendan Hanrahan]

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

 

[akphidelt]

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.

 

[Jim Thomlinson]

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)

 

[Kevin B]

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.

 

[Alan]

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.