Feb 21 2021 06:43 AM
Feb 21 2021 06:58 AM
@jtjohnston_quebec The problem is that SEARCH returns an error value if the term is not found.
So if the student answers anything but "survey", the first SEARCH causes the entire formula to return an error; the rest of the formula isn't evaluated.
Apart from that, I'd make the function return numbers such as 0.8 instead of text strings such as "0.8".
=IFS(IFERROR(SEARCH("survey",F2),0),1,IFERROR(SEARCH("survay",F2),0),.8,IFERROR(SEARCH("servay",F2),0),.5,TRUE,0)
Feb 21 2021 07:35 AM
Or
=IFS(ISNUMBER(SEARCH("survey",F2)),1,ISNUMBER(SEARCH("survay",F2)),0.8,ISNUMBER(SEARCH("servay",F2)),0.5,TRUE,0)
Feb 21 2021 08:10 AM
SolutionFeb 21 2021 11:07 AM
Feb 21 2021 05:49 PM
@Hans Vogelaarwhy did you use isnumber. why check to see if it is a number?
Feb 21 2021 06:01 PM
@Sergei BaklanI have an answer "Servay " that got evaluated as 0.
What's wrong?
=SUM(COUNTIF(F2,"*"&{"survey","survay","servay"} &"*")*{1,0.8,0.5})
Feb 21 2021 11:31 PM
It works
If you are on Excel which doesn't support dynamic arrays try to use SUMPRODUCT() instead of SUM(). Please check the file attached.
Feb 22 2021 04:22 AM
Feb 22 2021 06:09 AM
Sorry, can't help with Google Sheets, have no idea where it could be the difference.
Feb 21 2021 08:10 AM
SolutionAs variant
=SUM(COUNTIF(F2,"*"&{"survey","survay","servay"} &"*")*{1,0.8,0.5})