Thread: Sum based on partial contents of cells in another collumn

Please see the attached file for an example.

In my real project I have a lot more data, filling thousands of rows. One column lists the names of people involved in a project, the other lists project costs. I want to sum the costs of all projects that in any way included "Kevin."

Normally I would use SUMPRODUCT. The problem is, in this instance, I can't match exact cell contents because some entries in the name collumn include just "Kevin," while others include "Kevin" and several other names. I want the total costs of all the projects that included "Kevin," whether or not he was acting alone.

I would be happy to provide further clarification if necessary. This question may be addressed elsewhere in the forums, or in the help files, but I've been struggling to find the relevant entries because I don't know what to search for.

Thanks in advance...

No sample attached.

Remember that you can use Wildcards...

=SUMIF(A:A,"*Kevin*",B:B)

you may have issues with delimiters but we'd need to see an indicative sample first.

So - less watertight version:

=SUMIF(C:C,"*Kevin*",B:B)

more watertight but more expensive

=SUMPRODUCT(--(ISNUMBER(SEARCH(" Kevin "," "&C2:C12&" "))),B2:B12)