Sumif or vlookup on multiple partial strings

Hi

Not sure what formula is required here so your help would be massively appreciated. I'm using 2007.

I've attached a small spreadsheet example of what's required.

I need to gather costs from a range of projects. In this example, I need to collect the Staff Costs into cell C20, from all of the projects codes in column A, which begin with TMOL.0001, i.e:

TMOL.0001.01
TMOL.0001.02
TMOL.0001.03
TMOL.0001.04

So I think I need a formula to look at the partial range of the string "TMOL.0001" only, but will pick up all 4 of the projects.

For single project codes, I'd use a sumif or vlookup which is fine for single look ups, but this is a multi look up, on a range of project codes containing some of the same string of characters.

I realise a simple SUM range would also work, but this is not practical as the solution will be used across multiple (hundreds of) projects and I need make it the process automated. Additional projects will also be added with the same strings so I'll need to be able to capture them in future too..

Help me, please




---------------------------------------------

Updated...

The solutions from Colin and Blake 7 below work perfectly, but...


I'm pulling in data from three separate (private) spreadsheets into one consolidated (public) spreadsheet, using the SUMIF formula

However the formulae do not work in the public file, unless the three private files are open.
Apparently I need to use SUMPRODUCT for this to work when the other spreadhsets are closed.

Please could someone recommend a SUMPRODUCT formula which will work with the attached, rather than SUMIF.

Many thanks.

Hi,

There are three wildcards which can be used with SUMIF: * # ~

* represents any number of character and is of interest to us here.



This formula will sum B4:B13 when the cells in A4:A13 start with the text in C18.
=SUMIF(A4:A13,C18&"*",B4:B13)

So, if C18 contains TMOL.0001, it will sum any cells which start with TMOL.0001

There are several ways of achieving this. See attached.

My fave would be a pivot table. Please note that i have added an extra colum and named the projects. This way u can also use a sumif formula - also attached.

Hi Colin - i didnt refresh! as an aside - what do the # and ~ wildcards do?

Hi,

Hi Colin - i didnt refresh! as an aside - what do the # and ~ wildcards do?

# represents any single character
~ means that the following character should be treated as a literal, not as a wildcard. So you use it when you're summing against cells which contain * or # or ~.

Thank you both

Colin, great simple solution.

Blake 7 - Great name and suggestion of the pivot table.

I'll be using both of your suggestions for different parts of this work.

Many thanks!

********* UPDATED *************

Please see my update above.