Hmm, será mais fácil se você tiver algum tipo de coluna oculta logo após a coluna que contém as descrições e uma tabela de referência em algum lugar para as pontuações.
Por exemplo
A célula A2 contém 'Concordo'.
Intervalo de células Z1: AA5 contém a tabela de referência:
Highly agree 5
Agree 4
No opinion 3
Disagree 2
Highly disagree 1
Na célula oculta B2, coloque a fórmula:
=IFERROR(VLOOKUP(A2, $Z$1:$AA$5, 2, 0), 0)
O anúncio preenche esta fórmula até o final da tabela.
No final, você pode colocar uma soma lá:
=SUM($B$2:$B$30)
Supondo que a tabela termine na linha 30 da planilha.
Caso contrário, se isso não for possível, você pode usar uma função de matriz e 5 IFs aninhados, 1 para cada descrição:
=SUM((IF(A2:A30="Highly agree",5,IF(A2:A30="Agree",4,IF(A2:A30="No opinion",3,IF(A2:A30="Disagree",2,IF(A2:A30="Highly disagree",1,0)))))))
Esta fórmula estará funcionando corretamente somente quando você usar Ctrl + Deslocar + Enter porque é uma fórmula de matriz.
Outra opção que você pode usar é colocar a pontuação junto com as descrições, por exemplo, faça as descrições como esta:
5 Highly agree
4 Agree
3 No opinion
2 Disagree
1 Highly disagree
Em seguida, você pode usar a outra fórmula de matriz:
=SUM(LEFT(A2:A30,1)*1)
Similarmente, esta fórmula estará funcionando corretamente somente quando você usar Ctrl + Deslocamento + Enter porque é uma fórmula de matriz.