Assign a number to a performance word and total a row range

Question

Sorry about the cumbersome title i've got a blinding headache spending hours staring at a computer screen to work out the solution myself to no avail, i can barely think straight now.

All i want to do is assign a number to a sporting performance, then for an athlete have their scores totalled in a range on a row.

Example

PERFORMANCE AND NUMBER VALUES TO ASSIGN

Excellent 5

Very Good 4

Good 3

Average 2

Poor 1

THEN ON A ROW

Athlete Name 4 5 3 4 3 and the next cell would hold the SUM of those scores 19

Please believe me, i'm not lazy, i also take pleasure in working things out for myself, but i've gone through dozens of functions and my head hurts.

I'm using Excel 2019.

Thank You in advance of any assistance.

twifoo · Answer

brainbursteyeshurt&nbsp;Perhaps, this formula in F2 in the snapshot below satisfies your requirement:&nbsp;=SUMPRODUCT(LOOKUP(A2:E2,
{"A","E","G","P","VG"},
{2,5,3,1,4}))&nbsp;When copied down rows, the results will look like these:You may play with the formula in the attached file.&nbsp;

brainbursteyeshurt · Answer

So sorry i told you i wasn't thinking straight. In the cells of the row with the Athlete Name, i have, instead of the numbers, E, VG, G, A, P, so i wish those letters to DISPLAY, but in the last cell the number total to display.

Please note the examples above is a a rough one to 'try' and get across what i wish to do, it's not my actual document. However the query will help out anyone who wishes to assign a number to a word or any text statement, and then use the SUM in many ways.

brainbursteyeshurt · Answer

Huge apologies, as it seems i didn't reply and offer thanks for this at the time. I may have at the time settled on either a non-optimal alternative solution for speed or, using your example, as i came upon a glitch, something similar but certainly helped by your example.
Anyways Thank You very much and also to Riny above.

riny_van_eekelen · Answer

brainbursteyeshurt&nbsp;Perhaps not the most elegant solution, but in the attached workbook, you'll find a working example, using some named ranges.

Forum Discussion

Assign a number to a performance word and total a row range

4 Replies

Resources