Duplicate account codes that total amount
 

This should be an easy one, but nothing is coming to me! I have an expense
sheet where the user will list account codes and the amount spent for that
code. They can enter the same account code numerous times. I then have a
summary box that I want to auto populate the account codes used and the total
dollar amount spent for that code:

A B C
06000 50400 $15
06000 50200 $50
06000 50400 $26
06100 50200 $100

I would like the summary box to show the following:

J K L
06000 50400 $41
06000 50200 $50
06100 50200 $100

I cannot figure out the formula that will combine the duplicates and then
total the amount.

Duplicate account codes that total amount
 

Have a look at Pivot Tables. Worth while learning because if you can come to
grips with them, you will find numerous uses for them in the future.

--
Regards,

OssieMac


"MMM" wrote:

This should be an easy one, but nothing is coming to me! I have an expense
sheet where the user will list account codes and the amount spent for that
code. They can enter the same account code numerous times. I then have a
summary box that I want to auto populate the account codes used and the total
dollar amount spent for that code:

A B C
06000 50400 $15
06000 50200 $50
06000 50400 $26
06100 50200 $100

I would like the summary box to show the following:

J K L
06000 50400 $41
06000 50200 $50
06100 50200 $100

I cannot figure out the formula that will combine the duplicates and then
total the amount.

Duplicate account codes that total amount
 

Thank you, but I have tried a pivot table and it is not going to work the way
I need it to. Is there a formula that I can use instead?

"OssieMac" wrote:

Have a look at Pivot Tables. Worth while learning because if you can come to
grips with them, you will find numerous uses for them in the future.

--
Regards,

OssieMac


"MMM" wrote:

This should be an easy one, but nothing is coming to me! I have an expense
sheet where the user will list account codes and the amount spent for that
code. They can enter the same account code numerous times. I then have a
summary box that I want to auto populate the account codes used and the total
dollar amount spent for that code:

A B C
06000 50400 $15
06000 50200 $50
06000 50400 $26
06100 50200 $100

I would like the summary box to show the following:

J K L
06000 50400 $41
06000 50200 $50
06100 50200 $100

I cannot figure out the formula that will combine the duplicates and then
total the amount.

Duplicate account codes that total amount
 

You can concatenate the first 2 columns and then use a pivot table with only
2 columns of data and it works perfectly.

You can then select all the pivot table and copy-paste special-values.
Then move the totals over one column to make a blank column in between the
concatenated labels and the totals and then apply text to columns to separate
the first 2 coloumns again.

Suggest that when you concatenate the 2 columns that you also insert a space
that will then be the delimiter for the text to columns. Example:- =A2&" "&B2


--
Regards,

OssieMac


"MMM" wrote:

Thank you, but I have tried a pivot table and it is not going to work the way
I need it to. Is there a formula that I can use instead?

"OssieMac" wrote:

Have a look at Pivot Tables. Worth while learning because if you can come to
grips with them, you will find numerous uses for them in the future.

--
Regards,

OssieMac


"MMM" wrote:

This should be an easy one, but nothing is coming to me! I have an expense
sheet where the user will list account codes and the amount spent for that
code. They can enter the same account code numerous times. I then have a
summary box that I want to auto populate the account codes used and the total
dollar amount spent for that code:

A B C
06000 50400 $15
06000 50200 $50
06000 50400 $26
06100 50200 $100

I would like the summary box to show the following:

J K L
06000 50400 $41
06000 50200 $50
06100 50200 $100

I cannot figure out the formula that will combine the duplicates and then
total the amount.