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

1. ## 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. ## 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!

3. ## 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?

4. ## 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.

5. ## 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. ## 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. ## Re: Create a Sum based on cells mapping of a text value to a number

Thank you for the feedback.

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