Select Column Range

Natalia06

I have a form where there are 120 columns and collaborators enter the information through their department dynamic views. For easy math, let's assume each department has 20 columns to fill out. The last column is a checkbox such that if all 120 columns are filled out (essentially not blank), this check box will be marked. I see a couple ways to do this without a helper column:

1. Formula along the syntax of =IF((COUNT([Column1]@row, [Column 2]@row, [Column3]@row, … ,[Column 120]@row)/120)=1, 1, 0) ***I ran into the 4K character limit
2. Formula along the syntax of IF(AND(ISBLANK([Column1]@row:[Column120]@row), 1, 0) **NOT WORKING. DOES THIS WORK IN SMARTSHEET?
3. Automated Workflow so that the conditions are all the columns are not blank but is there a limitation on how many conditions there are?

Paul Newcome

You would use a COUNTIFS across all columns and count how many are blank. If that result is zero, then all cells are filled out.

=IF(COUNTIFS([Column1]@row:[Column120]@row, @cell = "") = 0, 1)

Paul Newcome

You could try an IFERROR.

=IFERROR(original_formula, "")

Paul Newcome

You would use a COUNTIFS across all columns and count how many are blank. If that result is zero, then all cells are filled out.

=IF(COUNTIFS([Column1]@row:[Column120]@row, @cell = "") = 0, 1)

Natalia06

Thank you for the solution Paul. I tried it and it worked. My question is, when the form is not completed, I am receiving the error: "#INVALID DATA TYPE" as columns 1-120 is a mix of text/numbers, drop down menus, and dates. My issue is that this form will be audited and seeing the error may raise flags. Is there a way to keep the checkbox unchecked with using IFERROR() before the IF statement?

Paul Newcome

You could try an IFERROR.

=IFERROR(original_formula, "")

Natalia06

Thank you!

Paul Newcome

Happy to help. 👍️