SORTING BY "text"

[tazhearts]

I have hit uppon another problem which is as follows:-

My banking transactions are set out on excel as follows:



Date Source
Amount
24-Nov-03 TESCO STORE 2387 DALKEITH $191.61
17-Oct-03 MARKS & SPENCER EDINBURGH 291 $28.00

etc. etc.

What I would like to do is work out how much money the wife has spen
at various outlets. For example how much has she spent at "TESCO" ove
a set period of time like a month.

Is there some way or formula of identlfying every time a certian wor
appears in a range of cells eg. "Tesco" and adding together the tota
amount spent over a certian period of time.

If there is I would be very impressed. My wife may not as I'll be abl
to track her spending more but it would be a geat way to calculate foo
budgets etc.

All suggestions greatfully recieved.

P.S. I am a relative newcomer to excel so please explain any answer a
you would to an idiot, preferable giving examples.

Cheers

tazheart

 

[Trevor Shuttleworth]

Try something like this:

=SUMIF(A1:A6,"*tesco*",B1:B6)

Regards

Trevor

 

[tazhearts]

I have hit uppon another problem which is as follows:-

My banking transactions are set out on excel as follows:


Date Source
Amount
24-Nov-03 TESCO STORE 2387 DALKEITH $191.61
17-Oct-03 MARKS & SPENCER EDINBURGH 291 $28.00

etc. etc.

What I would like to do is work out how much money the wife has spent
at various outlets. For example how much has she spent at "TESCO" over
a set period of time like a month.

Is there some way or formula of identlfying every time a certian word
appears in a range of cells eg. "Tesco" and adding together the total
amount spent over a certian period of time.

If there is I would be very impressed. My wife may not as I'll be able
to track her spending more but it would be a geat way to calculate food
budgets etc.

All suggestions greatfully recieved.

P.S. I am a relative newcomer to excel so please explain any answer as
you would to an idiot, preferable giving examples.

Cheers

tazhearts

 

[Gord Dibben]

taz

One method would be to select the three columns and Data>Filter>Autofilter

Put titles in row 1 of the sheet. Date, Store, Spent then run the Autofilter.

Use the drop-down arrow on the date column to custom filter for your dates
range. Less than, Greater than, etc. Note the AND/OR dialog box

Then filter on your store column for TESCO

Somewhere on the sheet, say D2, enter this formula...

=SUBTOTAL(9,C2:C100)

I am assuming the range of items in spent column extends no further than C100.
Adjust to suit.

The SUBTOTAL Function sums only visible items, not the filtered-out items.

You can also investigate the uses of Pivot Tables for this kind of project.

Gord Dibben XL2002