COUNT() not counting...

[1minManager]

I have an app that asks a series of questions, each being an ENUM of "Yes" or No".  I need to calculate what percentage of the test is complete.  The inital part of this is counting how many questions have been answered.  In shorter tests I've used something like this:

(
   IF(ISNOTBLANK([Q1]),1.00,0.00)+
   IF(ISNOTBLANK([Q2]),1.00,0.00)+
   IF(ISNOTBLANK([Q3]),1.00,0.00)+
   IF(ISNOTBLANK([Q4]),1.00,0.00)+
   IF(ISNOTBLANK([Q5]),1.00,0.00)
)/5

But appsheet errors if you go more than about 40 questions with a message about the maximum amount of nesting.  So I changed it over to this:

COUNT(
   LIST([Q1],[Q2],[Q3],[Q4])
)

Which straight away started behaving weirdly.  Firstly COUNT(LIST(...)) with an empty list is blank and not zero.  Ok, sorted that with:

IF(
   ISBLANK(LIST([Q1],[Q2],[Q3],[Q4]),
   0,
   COUNT(LIST([Q1],[Q2],[Q3],[Q4]))
)

 But then I was always getting a count of 2 with a list of {Yes,No,Yes,No}.  After much digging for some reason is was ignoring Q1 & Q2.  So I tried spliting it into two virtual columns to try and diagnose further:

Test Complete = LIST([Q1],[Q2],[Q3],[Q4])
Test Count = 
IF(
   ISBLANK(LIST([Q1],[Q2],[Q3],[Q4]),
   0,
   COUNT(LIST([Q1],[Q2],[Q3],[Q4]))
)

So now i see this:

Yet if I point Test Count at Test Complete like this

IF(
   ISBLANK([TEST Complete]),
   0,
   COUNT([TEST Complete])
)

Then it works fine...

Tried using SPLIT() and even LIST(TEXT([Q1],... on the assumption this was some data type error.  But its got me stumped.  Any ideas?

[JSO]

I think it's a good case to use a bot that calls a function in Google Script.

[AleksiAlkio · Accepted solution]

This is the known challenge with the COUNT() expression when using "inside" of the record (without SELECT() etc.). It counts items even they are blanks. The same will happen for example with the combination of normal column and EnumList. One way to do this is for example..

COUNT(EXTRACTHASHTAGS(SUBSTITUTE(SUBSTITUTE(TEXT(LIST([Q1],[Q2],[Q3],[Q4])),"Y","#Y"),"N","#N")))