Count all the Yes, No, N/A for scoring

[Qwaser]

Hi all, fairly new to AppSheet.
Trying to create an inspection checklist with 100 questions. Each question will have Yes/No/N/A, using enum value type.

What I am trying to do is add one point to the total score if the value selected is Yes. (I will divide the point by the total number of questions to get the average)
If they select N/A, then that question will not count towards the total score. (total # of question will get minus by how many N/As)

I have been using 100 IFS statement in a virtual column to calculate the score.
Like so:
IFS([q6] = “Yes” ,1) +
IFS([q7] = “Yes” ,1) +
IFS([q8] = “Yes” ,1) +
IFS([q9] = “Yes” ,1) +
IFS([q10] = “Yes” ,1) +
IFS([q11] = “Yes” ,1) +
I am just abbreviating the question with q6, q7, q8, but in reality the question is long.

Then using another 100 IFS to count the N/A
IFS([q6] = “N/A” ,1) +
IFS([q7] = “N/A” ,1) +
…

My app sometimes wouldn’t load, because there are too many IFS statements in one virtual column. Everytime I try to edit the questions, it is also a pain.

Just want to know if there is a simpler solution for this.

Thanks in advance,

[Bahbus]

COUNT(FILTER(“QuestionsTable”, [AnswerColumn]=“Yes”))

[Steve · Accepted solution]

The way you’re currently doing it–with huge IFS() expressions–is the only way to do it if all of the responses are contained in a single row, as it appears they are. @Bahbus’ suggestion would work if you structure your responses across multiple rows.

[Qwaser]

Thank You, I guess I will have to do it the hard way.

One question, is there a limit as to how many IFS expression can be in a virtual column, because the app won’t load if i go over 100 IFS expression, I have to separate into 2 or more VC.

[Steve]

I don’t know.

[Bahbus]

Coming back to this, if you are hell bent on doing this the hard way, then I would create a series of VCs to handle small chunks at a time. For example, if the form is 100 questions long, I would separate the form into multiple pages - lets say 10 pages, 10 questions each. I would then do a VC for each group of 10. Then you should be able to easily sum the 10 VCs together. If nothing else, this should make it slightly easier to manage in the long run.