Hello,

I have the following problem:

I would like to create a sheet that will track my credit expenses.
I have all my credit card expenses in an excel sheet and I would like to sum it in the following manner:

Business name Amount
-------------- -------
Wall Street J 100
New York T 99
Albertsons 500
Seas Candies 39.95
BP Fuel 56
...

I would like to sum it up to the following categories:

Newspapers 199
Food and Sweets 539.95
Gas 56

I thought about using SUMIFS per each category but then I'd be adding every possible business name into the categories list which is impossible.

I can create an intermediate list of business names by category:

Food And sweets
----------------
Albertsons
Seas Candies

Gas
-----
BP
Shell

Newspapers
-------------
Wall Street J
New York T

as many of the businesses are repetative, but how can I use this intermediate list?
I mean - how can I use the businesses under category "Newspapers" to sum up values related to newspapers from my credit statement ?

Thanks!

I would add another sheet (and hide it??).

I'd use two columns. The first column contains the business name and the second
column would contain the category.

Then I'd add a column to the the first sheet that uses =vlookup() to retrieve
the category so I could use that in the subtotal/pivottable processing.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

On 04/03/2012 15:25, Assaf Nathan wrote:
Hello,

I have the following problem:

I would like to create a sheet that will track my credit expenses.
I have all my credit card expenses in an excel sheet and I would like to
sum it in the following manner:

Business name Amount
-------------- -------
Wall Street J 100
New York T 99
Albertsons 500
Seas Candies 39.95
BP Fuel 56
..

I would like to sum it up to the following categories:

Newspapers 199
Food and Sweets 539.95
Gas 56

I thought about using SUMIFS per each category but then I'd be adding
every possible business name into the categories list which is
impossible.

I can create an intermediate list of business names by category:

Food And sweets
----------------
Albertsons
Seas Candies

Gas
-----
BP
Shell

Newspapers
-------------
Wall Street J
New York T

as many of the businesses are repetative, but how can I use this
intermediate list?
I mean - how can I use the businesses under category "Newspapers" to
sum up values related to newspapers from my credit statement ?

Thanks!





--
Dave Peterson