Formula for text columns convert to sum?
I'm trying to help build a sort of prioritization scorecard into a sheet. We have 4 columns, each with possible values of (example) "A", "B" or "C", and would like to have another column that sums each based on assigning a number value to each dropdown option.
So for example, the columns and their possible dropdowns:
Column 1 - A, B, C
Column 2 - A, B, C
Column 3 - A, B, C
Column 4 - A, B, C
Where A = 1, B = 2, C = 3
IF Column 1 = A, Column 2 = B, Column 3 = A, and Column 4 = C, the total value presented would be 7 (1 + 2 + 1 + 3).
I'm aware we could add 4 additional columns to present the numerical value attached to each, then a 5th column with the sum of those. However, I'll be frank: this is a monster sheet and adding even more columns when I'm hoping for a "simple" formula makes me want to cry.
Any suggestions? From what I'm reading on other forum posts I *think* this is impossible but would really appreciate a confirmation. Thanks!