Text Search
 

I have a downloaded list of charges on my credit card that I would like to
catagorize.

The descriptions are like: ILLIANOS Ristorante (Dining and Entertainment)

I have most of the entries summarized by catagory by using this:

=IF(TYPE(SEARCH("Dining",$D19,1))=1,$G19,"") where the description is in D
and the amount is in G

but I want to total a group of charges under a single category, such as:
Any description that has: software or hardware or spyware or Fry's contained
anywhere should be selected.

Since Search and Find produce an error if the string is not found, each test
needs a TYPE test to prevent errors and makes an OR list cumbersome.

Is there a simplier way using an index list or something else??

Thanks IA
GWB



2/1/2009 ILLIANO'S RISTORANTE(Dining and Entertainment) $52.00

Text Search
 

See if this is what you had in mind.

....................A.....................B
1...Spyware Blaster............20
2...Sears............................52
3...Fry's Online..................10
4...Who's Hardware..........10
5...AAA Software.............29
6...TipTop Cafe................32

To get the sum of the combined categories: spyware,Fry's,hardware,software

List the categories in a horizontal range of cells:

D1:G1 = spyware,Fry's,hardware,software

Then:

=SUMPRODUCT((ISNUMBER(SEARCH(D1:G1,A1:A10)))*B1:B1 0)

--
Biff
Microsoft Excel MVP


"George W. Barrowcliff" wrote in message
...
I have a downloaded list of charges on my credit card that I would like to
catagorize.

The descriptions are like: ILLIANOS Ristorante (Dining and Entertainment)

I have most of the entries summarized by catagory by using this:

=IF(TYPE(SEARCH("Dining",$D19,1))=1,$G19,"") where the description is in
D and the amount is in G

but I want to total a group of charges under a single category, such as:
Any description that has: software or hardware or spyware or Fry's
contained anywhere should be selected.

Since Search and Find produce an error if the string is not found, each
test needs a TYPE test to prevent errors and makes an OR list cumbersome.

Is there a simplier way using an index list or something else??

Thanks IA
GWB



2/1/2009 ILLIANO'S RISTORANTE(Dining and Entertainment) $52.00