+ Reply to Thread
Results 1 to 5 of 5

Solved: Sum values associated with keywords inside cell

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    57

    Solved: Sum values associated with keywords inside cell

    I have a spreadsheet in which I need to do something unusual.

    The setup:
    Each cell will contain a list of keywords. Each keyword is worth a specific value (1-4). A cell may contain any number of keywords in any order, however each keyword only repeats once.

    Data sheet
    source value
    Bob 1
    Mary 3
    Hill 2
    Brown 4

    Results sheet
    input value list value sum
    Phil,Bob,Brown


    Part1 :Getting a list that transposes the words in the cell to a list of associated values.

    In the results sheet I expect the value list to return 1, 2, 4

    I've done this 2 ways but I feel both are ugly and nonuser friendly, would be great if someone had a better/simpler way to do this.

    These work but are ugly:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Part2 : Sum up the associated values for every word in the cell.

    [S]I've looked at sumifs and sumproduct but I couldn't get this to work! [/S]

    [Formula]
    =SUMPRODUCT(--ISNUMBER(SEARCH(Data!A2:A5,A2)),Data!B2:B5)[formula]


    Note: I have an example spreadsheet but the upload isn't working.
    Last edited by witchcraftz; 11-17-2021 at 04:37 PM.

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,054

    Re: Sum values associated with keywords inside cell

    Please follow the instructions at the top of the page in the yellow banner for uploading a sample file with mocked-up solutions.

  3. #3
    Registered User
    Join Date
    01-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Sum values associated with keywords inside cell

    I think the upload is being blocked by done kind of security. The info advice should be enough for a potential answer though.

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,054

    Re: Sum values associated with keywords inside cell

    Please try again. Follow the instructions carefully and you should be able to upload a file.

  5. #5
    Registered User
    Join Date
    01-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    57
    Quote Originally Posted by Gregb11 View Post
    Please try again. Follow the instructions carefully and you should be able to upload a file.
    The solution is :

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do you create a range using the values inside of a specific cell?
    By cheeseleweese in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2020, 05:50 PM
  2. Track Values in a Cell with a Neighbor Cell Containing Specific Keywords, how?
    By Rapmastac2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2019, 02:09 PM
  3. Showing min and max values as a line inside a cell
    By emma390 in forum Excel General
    Replies: 10
    Last Post: 02-09-2016, 10:12 PM
  4. [SOLVED] Search for keywords and copy rows containing keywords to new sheet
    By lenorsk in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-15-2013, 06:54 AM
  5. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  6. Help listing keywords inside sentences.
    By energykick in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-15-2012, 12:09 PM
  7. Formating inside cell values
    By announce in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2011, 09:28 AM

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