# Count number of Responses

For example, I have a form with six questions(q1 to q6) with select_one options( Yes=1 and No=0). I want to calculate total count of "Yes" responses and total count of "No" responses at the end of the form using "note". What should I do it using xls?

Hello @Justine_Govela,

There are several ways to go about this, however, given your questions are only `Yes or No`, the quickest way would be:

• create a `calculate` field and name it `yes_q` to `sum` all questions i.e.: `\${q1}+ \${q2}+ \${q3}+...+\${qn}` where `n` is the max number of questions you have in your set.This simply gives you the same number of questions answered with `yes`.
• Now create another `calculate` and name it `no_q` to get the difference from `n` and the sum of `YESs` you previously calculated. The calculation would be `6 - \${yes_q}`.
• In the note, you can now display the result as: `you answered \${yes_q} qiestions with "yes" and \${no_q} questions with "no"`

Careful though, the method above would only work if all questions are required and have no relevant set. If you have relevant, then make sure that all skipped questions are assumed to be `no or 0` by using `coalesce(\${q1},0)+coalesce(\${q2},0)+...+ coalesce(\${qn},0)` in the calculation of `yes_q` field

Hoping this helps,

Cheers,
Jules R

Thank you for the feedback, but I used Yes and No as an example but in real case, my options are (Poor, Good, and Fair) can you please help with that?

Hello @Justine_Govela,

Sorry I took long to revert.

So yes, you can use any answers. Using this sample form question_scores_jr.xlsx, you can be able to score and sum any number of questions with any number of options.

The technique uses:

• One `select_multiple` question to list all possible statements
• One `repeat()` to iterate through all selected statements (answered with Poor, Good or Fair)
• Some template `calculate` field to gather each answer as `1 or 0`
• One `group()` and `integer` field to summarise answers in the repeat

The initial select_multiple statement question can be set to select_all by default. while this method is extensible, you will need basic understanding of `repeat` structure and the data it yields. You will also need to perform some wrangling once the data has been exported. At least you get the scores you initial wanted. See below images of how the form will look on you device.

Hoping this helps,

Cheers,
Jules R

1 Like