# 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  Register To Reply

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!  Register To Reply

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?  Register To Reply

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.  Register To Reply

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  Register To Reply

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  Register To Reply

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

Thank you for the feedback.  Register To Reply