+ Reply to Thread
Results 1 to 4 of 4

Sum based on partial contents of cells in another collumn

  1. #1
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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...
    Attached Files Attached Files
    Last edited by tpillow; 06-09-2009 at 07:28 AM. Reason: clarity & brevity

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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.

  3. #3
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

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

    My bad. I attached a sample to my original post. The actual prject contains proprietary data, so this version is highly simplified.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    So - less watertight version:

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

    more watertight but more expensive

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1