+ Reply to Thread
Results 1 to 7 of 7

Create a Sum based on cells mapping of a text value to a number

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Create a Sum based on cells mapping of a text value to a number

    Sorry, the title is not that clear, but this is what I want to do...

    I have a spreadsheet that has a row of cells that look like this, one for each competitor

    1st 2nd 3rd

    These are results from a competition, and in the third case no result was obtained. Each result has an associated score, which is defined on a separate sheet (this also defines the validation for the cells);

    0
    1st 5
    2nd 4
    3rd 3
    Commended 1

    What I want to be able to do is create a sum of the scores for a competitor, based upon the placings displayed on the spreadsheet. So for the example given, where there are 4 results, one of which is blank, the competitor would score (5 + 4 + 3 = 12).

    Using the example above, the result would look something like this;

    Fred Bloggs 1st 2nd 3rd 12

    How would I go about this? There will be 12 columns of scores, and of course multiple rows, one for each competitor.

    Any help or guidance is much appreciated.

    Thanks in anticipation

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Create a Sum based on cells mapping of a text value to a number

    Create a lookup table somewhere in your workbook (let's say F4:G7):

    1st 5
    2nd 4
    3rd 3
    Commended 1

    Use a formula like this at the end of each row to do the calculation, copied down:

    =IFNA(VLOOKUP(F12,F$4:G$7,2,0),0)+IFNA(VLOOKUP(G12,F$4:G$7,2,0),0)+IFNA(VLOOKUP(H12,F$4:G$7,2,0),0)+IFNA(VLOOKUP(I12,F$4:G$7,2,0),0)

    You will need one VLOOKUP statement for each of the cells in the row that are in the range, whether empty or not - the formula deals with empty cells.

    This works, although there may be less cumbersome ways of achieving this, but it's a start!
    Last edited by AliGW; 04-20-2014 at 01:45 PM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Create a Sum based on cells mapping of a text value to a number

    Hello Nerdio, This would be much easier to answer if you can upload a sample book to show what you are trying to achieve.
    if you cannot update a workbook please let us know weather the spreadsheet which has a row of cells , one for each competitor is arranged in what manner ?
    Is it arranged with the name of competitor in the left hand side with the headers on the top marked as 1st, 2nd , 3rd so on Or is it a separate header with the names of competitors in the left?
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Create a Sum based on cells mapping of a text value to a number

    This might work. It uses IFERROR to take care of errors. If you have Excel 2013 a new function IFNA will do the same thing.
    Attached Files Attached Files
    Last edited by newdoverman; 04-20-2014 at 03:20 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Create a Sum based on cells mapping of a text value to a number

    This worked just fine (I use Excel 2010). Thanks

  6. #6
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Create a Sum based on cells mapping of a text value to a number

    Thanks for this reply. I did not make it clear that I am on Excel 2010, so am unable to test it. Thanks for your reply though

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Create a Sum based on cells mapping of a text value to a number

    Thank you for the feedback.

+ 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. vba macro filter based on a array of cells with text and number values
    By daviddg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2014, 04:39 AM
  2. Create a macro for mapping
    By buttnana in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-15-2014, 08:36 PM
  3. [SOLVED] Macro to save workbook - Create Directories / Sub-Directories Based using text in cells
    By kspeese in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2014, 01:21 PM
  4. Create mutiples Cells based on input number
    By perrierf in forum Excel General
    Replies: 3
    Last Post: 10-28-2011, 08:11 AM
  5. insert text in cells based on maximum row number
    By twrizzo in forum Excel General
    Replies: 1
    Last Post: 08-29-2007, 05:06 PM

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