Survey completeness problems in Google Sheets conditional statement

Question

I'm trying to have information about the completeness in some survey results exported in to a Excel Format. I'm using Google Sheets, as every survey there is questions and subquestions the subquestions have a conditional, Example: 4. How are you today? multiple choice answers: Good, Bad, Prefer not to say, so there we have 3 answer options if we click good there is a conditional and the subquestion will be: Why?.

So in my survey there is 8 questions and question 4, 7 and 8 has conditional questions if someone answer "Yes".

Now here is my problem: to calculate the percentage of completeness I used this relation --> number of inputs in the survey/number of expected answers.

But as I mentioned before the conditional affects this expected answers this Variable is dynamic depending on the answers from question 4, 7 and 8. So I would like to obtain this Variable for every case, if someone put information will have an ID if we have 20 persons doing the survey we will have 20 ID's.

So for every record of answers the number will change depending on the inputs from Question 4, 7 and 8. I have prepared a document in Google sheets will the full approach that I tried but is still hard to have it right I would like to have some help with this.

I would like to use this kind of formula:

=COUNTIF(AI2:AR2,"Yes")>0

This tell me if in the range of the Question 4 there is a "Yes" to activate the subquestions. Then : `IF(TRUE, 11, 1) Which should be the count if in Q4 there is a Yes at least for that row. That's a little simpler but is the approach I would like to do.

This is the points:

1. if any of these parts are filled with YES or NO that's 1 point for whole Q4(Correct)
2. every part of SUBquestion count as 1 (Correct)
3. if SUBquestion is filled does MAINquestion count as the sum of MAIN QUESTION and number of SubQuestions. Case of Q4: Answer was Yes, the count will be 11 the 10 parts plus the Main one. If Answer was No the count will be 1.

Link to Spreadsheet

In the file you will find more information.

Please some help with this I'm having days trying to solve it

Here is an image about it:

Answer 1

If I understood your question correctly this is the approach you want.

Try the following:

• For Q4: =IF(COUNTIF('Original Data Base'!AI2:AR2,"Yes")>0,11,1)
• For Q7: =IF(COUNTIF('Original Data Base'!BF2,"Yes")>0,2,1)
• For Q8: =IF(COUNTIF('Original Data Base'!BI2:BR2,"Yes")>0,11,1)

Drag down to rows below.

Then to get the percentage of completeness use:

=(SUM('Anwers formula'!B2:AD2)/SUM(C2:J2))*100

To explain this syntax -> This gets the (Answer count / Number of expected answers) X 100 to get the percentage.

Result:

This returns the same result as you have in your sample sheet "Last Step".

Or all together in one formula:

=(SUM('Anwers formula'!B2:AD2)/SUM(5+(IF(COUNTIF('Original Data Base'!AI2:AR2,"Yes")>0,11,1))+(IF(COUNTIF('Original Data Base'!BF2,"Yes")>0,2,1))+(IF(COUNTIF('Original Data Base'!BI2:BR2,"Yes")>0,11,1)))*100)

You can remove the * 100 and format it to percent value.

Let me know if this resolves your concern, or if you need help adjusting this to your spreadsheet.