How to count conditional questions in a survey using Google Sheets?

Other Questions Google Sheets
1

I need help with a tricky counting problem in my survey spreadsheet. The survey has Yes/No questions, and some Yes answers lead to extra subquestions. I want to count the total questions asked for each person, but it’s not straightforward.

Here’s the issue:

  • There are 7 main questions
  • Some questions have subquestions if answered Yes
  • The total count changes based on each person’s answers

For example:

  • Person 1 answers Yes to a conditional question, so their total is 8
  • Person 2 answers No, so their total stays at 7

I tried using =IF(B3="Yes", 1, "") but it doesn’t work right. It ignores the No answers and doesn’t count the main question in those cases.

Can anyone suggest a formula or method to accurately count both the main questions and conditional subquestions for each person? I’m open to any approach, whether it’s counting individually or getting a total. Thanks for your help!

2

I’ve encountered this issue in my survey work too. Here’s a straightforward solution:

Create a helper row at the top of your sheet. In each cell, put a 1 for main questions and 2 for conditional ones. Then use this formula for each person:

=SUMPRODUCT(B1:H1, --(B2:H2<>“”))

This multiplies your helper row by 1 for answered questions (non-blank cells) and 0 for unanswered ones, then sums the result.

It’s clean, doesn’t require extra columns, and automatically adjusts if you change your survey structure. Just update the helper row accordingly.

Remember to adjust the cell ranges to match your specific sheet layout.

3

I’ve tackled similar survey counting challenges before. Here’s a robust approach that worked well for me:

Create a helper column next to each main question. Use a formula like:

=IF(AND(NOT(ISBLANK(B2)), B2<>“Yes”), 1, IF(B2=“Yes”, 2, 0))

This assigns 1 for answered main questions, 2 for ‘Yes’ with subquestions, and 0 for blanks.

Then, sum these helper columns for each respondent’s total:

=SUM(I2:O2)

where I2:O2 are your helper columns.

This method accurately tracks both main and conditional questions, giving you a precise count for each survey participant. It’s flexible and easy to adjust if your survey structure changes.

4

hey, i’ve dealt with this before! try using COUNTIF to count all questions, then add a COUNTIF for ‘Yes’ answers. something like:

=COUNTIF(B2:H2, “<>”)+COUNTIF(B2:H2, “Yes”)

this counts non-blank cells (main Qs) and adds extra for ‘Yes’ answers. tweak the ranges to fit ur sheet. hope this helps!