Text Search

[George W. Barrowcliff]

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

 

[T. Valko]

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:B10)