+ Reply to Thread
Results 1 to 3 of 3

Sum Only Whole Words for Word Matrix Based on a Keyword List

  1. #1
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

    Sum Only Whole Words for Word Matrix Based on a Keyword List

    Example of Situation
    Create Artificial Spaces in Front of Range AND Get WHOLE WORDS ONLY.xlsx

    I have a list of phrases that I have broken down into single words and created a word matrix.

    What am I trying to achieve?
    Sum the intersection of word 1 and word 2.

    The problem?
    I want to sum WHOLE words only and NOT simple text strings.

    Example
    I have highlighted the total for the phrase "hipaa training" (21,933) in the attached. When I look at the intersection of "hipaa" AND "training", the total shows 22,389 and not 21,933. The reason for this is because of the phrase "hipaatraining" is being counted in this total. Since the word "training" in the phrase "hipaa training" is not a whole word but instead concatenated, I don't want to include these in the total sum.

    My current formula
    Please Login or Register  to view this content.
    What I somehow need to do is put artificial spaces at the beginning and ending of each phrase in the A:A range. Ex: " "&A:A&" " and also use this same methodology for the cell references for each word in the word matrix... but this is where I'm stuck. I don't know how.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Sum Only Whole Words for Word Matrix Based on a Keyword List

    Using your posted workbook...this regular formula, copied across and down, returns the Col_B value where the corresponding Col_A cell contains the Col_D word AND the Row_1 word.
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Sum Only Whole Words for Word Matrix Based on a Keyword List

    Hi Ron, your solution worked perfectly! I admit, I don't understand how your formula does what it does which shows my lack of ignorance for sumproduct. Thanks again!

+ 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. [SOLVED] Populate Keyword List based on Pattern
    By ypnOz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2013, 10:32 AM
  2. Macro To Find & Copy Rows Based On A Keyword List
    By Lilfish in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2013, 10:02 AM
  3. [SOLVED] Macro to display rows from another tab, based on 2 criteria (List and keyword)
    By KenzoMinamoto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2013, 09:17 PM
  4. Filter List by Keyword & copy the result in column by keyword as header
    By kitunga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2013, 07:16 AM
  5. Replies: 3
    Last Post: 09-28-2012, 11:12 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