Hi everyone I'm trying to count with a condition but some context before, I have a survey this survey has Yes/No Questions in some cases if you answer yes it appears a subquestion like: If yes please tell us why.

I'm counting the total of questions that are asked but this is a kind of dynamic number depending if the user with ID 1 answers Yes or No if he answers Yes this total number will be modified because the subquestion is one more question to count so if in My survey I have 7 questions and one of those 7 is Conditional will be added 1 more question so for ID 1 the total of questions asked was 8. But if ID 2 answer no the total will be 7 still.

I have an example with some questions and answers in this image:

I'm using this formula to count: =IF(B3="Yes", 1, "") but this ignores the No and I would like to have something like if Yes count the subquestion if not just count the question.

It's could be hard to understand let me know any doubts.

Help please could be any approach like counting individual or total.

CodePudding user response：

try:

={"Total"; INDEX(IF(A3:A10="",, 
 MMULT(IF({B3:B10, C3:C10&D3:D10, E3:E10}="", 0, 1), 
 SEQUENCE(COLUMNS({B3:B10, C3:C10&D3:D10, E3:E10}), 1, 1, 0))))}

update:

={"Total"; INDEX(IF(A3:A10="",, 
 MMULT({IF(C3:C10&D3:D10="", 0, 2), SEQUENCE(ROWS(A3:A10), 3, 1, 0)}, 
 SEQUENCE(COLUMNS(C3:C10&D3:D10) 3, 1, 1, 0))))}