Jul 13 2021 10:24 PM
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.
Jul 13 2021 10:32 PM
Jul 13 2021 11:12 PM
@brainbursteyeshurt Perhaps not the most elegant solution, but in the attached workbook, you'll find a working example, using some named ranges.
Jul 13 2021 11:36 PM
Perhaps, this formula in F2 in the snapshot below satisfies your requirement:
=SUMPRODUCT(LOOKUP(A2:E2,
{"A","E","G","P","VG"},
{2,5,3,1,4}))
When copied down rows, the results will look like these:
You may play with the formula in the attached file.
Nov 15 2021 06:56 PM