+ Reply to Thread
Results 1 to 2 of 2

find and return multiple matches in a table, then sum and report total

  1. #1
    Registered User
    Join Date
    East Coast, US
    MS-Off Ver
    Excel 2010

    find and return multiple matches in a table, then sum and report total

    I'm trying to automate a table that ranks criteria for a design process. The table itself contains standardized descriptions of the "wants" for the design, and then 'weights' the wants by perceived importance. I've attached the workbook to hopefully clarify things a bit.

    The weighting system works by giving each cell in the table a unique multiplier. Then, by adding up the numerical values from each instance of the "want" in the table, you get a final score for it. I'd like to have Excel do this automatically so if one of the values changes, the entire table doesn't have to be recalculated by hand.

    In English, the operation would be something like:
    1) Search table/array for <want1> and find all instances
    2) For each instance found, calculate: Multiplier*(Weightfactor/Rank)
    3) Sum the calculations from all instances and report in output "score" cell
    4) Do this for all listed "wants"

    I've been fooling around with Index, Vlookup, Match, ect. but I just can't find a way to do it with multiple columns to search through. I'm wondering if this can only be done with vba?
    UDesign template.xlsx

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    Victoria, Canada
    MS-Off Ver

    Re: find and return multiple matches in a table, then sum and report total

    If you are willing to ensure that the Rank range has no zeros (i.e. blank rows would require a nominal value of 100?), then in cell J23 you could use this formula:

    Please Login or Register  to view this content.
    copy/autofilled down.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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