Any workarounds for COUNTBLANK()?

Jon_S · 08-07-2019 09:35 PM

Hey everyone!

I would like to track the progress on the completion of a given entry’s fields - tracking if they are blank or not… Maybe in a VC. So it would look something like COUNTBLANK([_THISROW])

So if say my form has 10 questions and the user has so far submitted 1/10 - the result of would read ‘9’.

OBVIOUSLY i can use that expression in GSheets and be done with it - but i’m just curious about whether or not this could be possible using an Appsheet expression instead of relying on a spreadsheet formula.

I’m pretty sure i’m just being a bit thick here I’m sure someone has bumped into this one before.

Many thanks in advance!

MultiTech

This can be done, absolutely, and I would suggest creating an actual column to hold this information (instead of leaving it a virtual column).

The formula you would use is like this:

SUM(
  if(isnotblank([Column1]), 1, 0), 
  if(isnotblank([Column2]), 1, 0), 
  if(isnotblank([Column3]), 1, 0), 
  if(isnotblank([Column4]), 1, 0), 
  if(isnotblank([Column5]), 1, 0), 
  if(isnotblank([Column6]), 1, 0), 
  if(isnotblank([Column7]), 1, 0), 
  if(isnotblank([Column8]), 1, 0), 
  if(isnotblank([Column9]), 1, 0), 
  if(isnotblank([Column10]), 1, 0)
)

The whole things consists of a SUM() with an if statement for each column that you’re wanting to watch; each IF() statement looks at a specific column and if it’s blank or not records a 1 or 0 - and all of these are added together by the count.

Jon_S

Thanks for the reply - I thought about this but it’s not a particularly elegant solution…

Thanks anyways!

jovish_jose

I am using Count(select(table1[colum 1]…
Where the column 1 contains blank cells , which are also counted in the above expression.
Is there a way to count only non-blank cells of a list produced by select expression
?

Thanks in Advance…

Steve

Use FILTER() instead of SELECT():

COUNT(FILTER("table1", AND(ISNOTBLANK([column 1]), ...)))

jovish_jose

Thanks Steve… It worked…