You may often need to sum text values in Google Sheets by virtually assigning numerical scores to them. This is especially common in surveys, feedback forms, or performance assessments.

For example, you may have a table of product feedback from users, where ratings are given as text responses like:

“Exceptional”, “Very Good”, “Good”, “Fair”, and “Very Poor”

Each of these text values can be mapped to scores such as 5, 4, 3, 2, and 1 respectively.

But how do you calculate total or average scores from these text-based responses?

Let’s explore how to sum text values by mapping scores in Google Sheets using two real-world examples.

Example 1: Calculate Scores from Text Responses (User Ratings)

Here’s a simple table of product feedback collected from users:

Assume we map the rating text to the following scores:

To calculate scores from the text responses, use the formula below. Assuming your table is in range A1:B, with headers in row 1, enter this formula in cell C2:

=SWITCH(B2:B, "Excellent", 5, "Good", 4, "Fair", 3, "Poor", 2, IFERROR(1/0))

This formula maps each text to its corresponding score. The IFERROR(1/0) trick returns a blank (error) instead of 0 for any unmatched or empty responses, ensuring cleaner averages.

To calculate the average score from these text values:

=AVERAGE(
   SWITCH(B2:B, "Excellent", 5, "Good", 4, "Fair", 3, "Poor", 2, IFERROR(1/0))
)

To sum the text values instead:

=SUM(
   SWITCH(B2:B, "Excellent", 5, "Good", 4, "Fair", 3, "Poor", 2, IFERROR(1/0))
)

This is how you can sum text values by mapping scores in Google Sheets for feedback-type data.

Example 2: Sum Text Values (Student Performance) by Mapping Scores in Google Sheets

Suppose you have a table with students’ test ratings given as text values:

Let’s say we want to sum each student’s score based on this mapping:

Since text can’t be summed directly, we’ll map each value to its corresponding score using SWITCH.

Method 1: Row-by-row sum using SWITCH

In cell E2, enter:

=SUM(
   SWITCH(B2:D2, "Exceptional", 5, "Very Good", 4, "Good", 3, "Fair", 2, "Very Poor", 1, IFERROR(1/0))
)

Drag this down to fill the entire column. This will sum scores for each student based on their test responses.

Method 2: Use BYROW and LAMBDA (no dragging)

To apply the logic across the entire range automatically:

=BYROW(B2:D, LAMBDA(row, 
   SUM(
      SWITCH(row, "Exceptional", 5, "Very Good", 4, "Good", 3, "Fair", 2, "Very Poor", 1, IFERROR(1/0)))
   )
)

This outputs a column of total scores row-wise. But it might return trailing 0s for empty rows.

To return blank instead of 0s:

=BYROW(B2:D, LAMBDA(row, 
   LET(score, 
      SUM(
         SWITCH(row, "Exceptional", 5, "Very Good", 4, "Good", 3, "Fair", 2, "Very Poor", 1, IFERROR(1/0))), IF(score=0,,score)
      )
   )
)

Conclusion

When working with survey responses, feedback, or performance ratings stored as text, you can easily sum text values by mapping scores in Google Sheets using SWITCH, SUM, BYROW, and LAMBDA.

This approach helps you extract meaningful numerical summaries from qualitative input without manual conversion.

Related Resources

• Extract All Numbers from Text and Sum Them in Google Sheets
• Sum Cells With Numbers and Text in a Column in Google Sheets
• Using SUMIF in a Text and Number Column in Google Sheets