Search, Sum, Display?

  • Thread starter Thread starter Scot
  • Start date Start date
S

Scot

Is there a way to do this?

1. Col A represents the AMOUNT of individual expenses.
2. Col B represents the ACCOUNT # for each expense.
3. At the bottom, is the sum of each expense for each account. Acct 2540 =
$15. Acct 3620 = $9.00. Acct 3540 = $30.50. And the Sum of each account
is in ascending numerical order.

Is there a way to automatically search Col B, and Sum the expenses for each
account, then display those Sums as shown at the bottom?

INDIVIDUAL EXPENSES ----------
Col A Col B
AMOUNT ACCOUNT
$ 2.50 3620
$ 10.00 3540
$ 6.50 3620
$ 20.50 3540
$ 15.00 2540

SUM OF EXPENSES BY ACCOUNT ----------
Total Account
$ 15.00 2540
$ 30.50 3540
$ 9.00 3620
 
Okay, I'm lost. I can usually deal with Excel better than the average Joe,
but you've lost me here. What I've shown below is obviously a truncated
petty cash form. In this post, I've added specific row numbers to use as
the point of reference for discussion about my quest for knowledge. Here's
a more specific description of my quest:

1. I need a way of finding all unique account #s in Col B, Rows 1 - 5, then
displaying those accounts one time only, in ascending order, in Rows 8, 9,
10. In my example below, there are three unique acct #s, meaning that
duplicate acct #s are discarded. Those unique acct #s are 2540, 3540, 3620.
Each of them are displayed in ascending order beginning in Row 8.

2. For Col A, I need a way of finding all amounts for each acct #, and
displaying the sum next to its acct # at the bottom. In my example below,
we see $15 for a single occurrence of acct # 2540. $15 is then displayed
next to acct # 2540 in Row 8. For acct # 3540, there are two entries: $10
and $20.50; their sum is $30.50 and it is displayed next to acct # 3540 in
Row 9. For acct # 3620, there are two entries: $2.50 and $6.50; their sum
is $9 and is displayed next to acct # 3620 in Row 10.

3. TRANSLATION: In this example of a petty cash expense form, the
individual amounts, and their appropriate account #s will be the given. I
need the summary of each unique account number to display in the bottom,
rows 8, 9 and 10 in this example.
 
Okay, I'm lost. I can usually deal with Excel better than the average Joe,
but you've lost me here. What I've shown below is obviously a truncated
petty cash form. In this post, I've added specific row numbers to use as
the point of reference for discussion about my quest for knowledge. Here's
a more specific description of my quest:

1. I need a way of finding all unique account #s in Col B, Rows 1 - 5, then
displaying those accounts one time only, in ascending order, in Rows 8, 9,
10. In my example below, there are three unique acct #s, meaning that
duplicate acct #s are discarded. Those unique acct #s are 2540, 3540, 3620.
Each of them are displayed in ascending order beginning in Row 8.

2. For Col A, I need a way of finding all amounts for each acct #, and
displaying the sum next to its acct # at the bottom. In my example below,
we see $15 for a single occurrence of acct # 2540. $15 is then displayed
next to acct # 2540 in Row 8. For acct # 3540, there are two entries: $10
and $20.50; their sum is $30.50 and it is displayed next to acct # 3540 in
Row 9. For acct # 3620, there are two entries: $2.50 and $6.50; their sum
is $9 and is displayed next to acct # 3620 in Row 10.

3. TRANSLATION: In this example of a petty cash expense form, the
individual amounts, and their appropriate account #s will be the given. I
need the summary of each unique account number to display in the bottom,
rows 8, 9 and 10 in this example.


Frank Kabel said:
Hi Scot
one way: in your sum part enter the following (assuming it starts in
row 100)
=SUMIF($B$1:$B$90,B100,$A$1:$A$90)

you may also have a look at pivot tables who would create such reports
without formulas
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.cpearson.com/excel/pivots.htm

Frank
Click to expand...
 
Hi Scott
just enter the formula in A8
=SUMIF($B$1:$B$6,B8,$A$1:$A$6)
and copy down
for your example, when the unique account numbers are given in B8:B10
The problem is to extract unique account numbers and sorting them. So
if you have to find the account numbers automatically (e.g. you have a
large range), you should use pivot tables as this gets quite
complicated with formulas. Or you have to use macros.

Frank
 
Thanks. What am I doing wrong? When I put your formula in A8, and copy it
into A9 and A10, each cell returns a zero.
 
Hi Scot
what are your values in B8:B10. As I wrote this would work only if your
unique entries are alreday in this range B8:B10
 
Ah! That's what I was missing. My fault. Thanks. And I should use pivot
tables or macros to seek, extract, sort and display unique account numbers.
Got it. Well, pivot tables will be a new one for me. Thanks again; I
appreciate your help.
 
Back
Top