Spreadsheet help

shopbot

Please can I have some spreadsheet help?

I've created a spreadsheet that I download my monthly credit card transactions into. I'm using Google Docs.

I want to be able to group certain transactions together so I can track my spending against my budget.

I have been using the SUMIF command to do this. This works when the transaction always has the same title eg 'Asda'

However some retailers will show as 'Tesco Store 123, Tesco Store 234' etc

Is there way to make the SUMIF command incorporate an instruction similar to 'If this cell contains the word Tesco' then group together.

Thanks
SB

rmg1

You would probably need what's called a "helper" column.
You can translate the (in this case) "Tesco Store 123" into just "Tesco" using either vlookup or index/match (you'd need the full names somewhere else).
You can then use SUMIF against the helper column and carry on as normal.

Stompa

You can use wildcards, so where you would previously have used:

=SUMIF(A1 : A5,"Tesco Store 123",B1:B5)

instead use:

=SUMIF(A1 : A5,"*Tesco*",B1:B5)

(though without the spurious spaces around the colons).

shopbot

Thanks rmg1 and stompa.

Stompa's solution looks to work best for me at the moment as I may not always be shopping in the same set of locations.

Please can I ask a follow up question?

I've set up a single wildcard successfully - how can I change that to 'All Entries that contain Tesco, Asda or Sainsbury' etc?

Stompa

shopbot wrote: »

I've set up a single wildcard successfully - how can I change that to 'All Entries that contain Tesco, Asda or Sainsbury' etc?

It's probably not the best solution, but I guess you could simply add them together, so:

=SUMIF(A1 : A5,"*Tesco*",B1:B5)+SUMIF(A1 : A5,"*Asda*",B1:B5)+SUMIF(A1 : A5,"*Sainsbury*",B1:B5)

(though that does assume that you never have an entry that contains both Tesco & Asda say).

stevemcol

=SUM(SUMIF(A1 : A5,{"*asda*","*tesco*","*sainsbury*"},B1 : B5))

shopbot

Thanks stevemcol and Stompa. Have got those working.

SB