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