# Create the total number of blank non contiguous cells in a specific row

Options
edited 04/28/22

I am new to smartsheets and am looking to do the following

1) I have 10 columns that are not next to each other (ex Column1, Column4, Column7,Column25, Column 41, etc)

2) For each row I want to be able to get the number of cells out of the 10 columns that are blanks displayed in its own cell

3) display the cell value above as a % (sum of the blanks for the 10 non contiguous columns/10) for each row

#2 is obviously my issue. Any help or guidance would be incredibly appreciated.

Thanks

Dax

• ✭✭✭✭✭✭
Options

Here's a way to do it with less helper columns.

Example sheet:

Primary column formula finds the number of cells which aren't blank (its looking for the Data 1, Data 2, etc):

=SUM(COUNTIF([Data 1]@row, <>"..."), +COUNTIF([Data 2]@row, <>"..."), +COUNTIF([Data 3]@row, <>"..."), +COUNTIF([Data 4]@row, <>"..."), +COUNTIF([Data 5]@row, <>"..."), +COUNTIF([Data 6]@row, <>"..."), +COUNTIF([Data 7]@row, <>"..."), +COUNTIF([Data 8]@row, <>"..."), +COUNTIF([Data 9]@row, <>"..."), +COUNTIF([Data 10]@row, <>"..."))

Then a simple formula to find the # of blank columns (it's assuming you've got 10 columns you were checking to see if were blank):

=SUM(10 - [Primary Column]@row)

Then a column formula to find the % blank:

=SUM([Blank Count]@row / 10)

• ✭✭✭✭✭
Options

For each of your columns, create a helper column that tests whether the column is blank, so columns named like: test2 and test5

In each column, this formula:

test2 is =IF(ISBLANK([Column2]@row), 1, "")

test5 is =IF(ISBLANK([Column5]@row), 1, "")

Where you put the appropriate column name to check.

Then a SUM column that references each of the test columns:

=SUM([test2]@row, [test5]@row)

Once you are done, you can replace every reference in the SUM column with the formula that underlies the column, and end up with:

=SUM(IF(ISBLANK([Column2]@row), 1, ""), IF(ISBLANK([Column5]@row), 1, ""))

And then you can delete all of the helper columns.

When I do this, I name my column really short names so I can keep things straight, and I rename them when it is all working. Renaming the column will update the formula.

• ✭✭✭✭✭✭
Options

Here's a way to do it with less helper columns.

Example sheet:

Primary column formula finds the number of cells which aren't blank (its looking for the Data 1, Data 2, etc):

=SUM(COUNTIF([Data 1]@row, <>"..."), +COUNTIF([Data 2]@row, <>"..."), +COUNTIF([Data 3]@row, <>"..."), +COUNTIF([Data 4]@row, <>"..."), +COUNTIF([Data 5]@row, <>"..."), +COUNTIF([Data 6]@row, <>"..."), +COUNTIF([Data 7]@row, <>"..."), +COUNTIF([Data 8]@row, <>"..."), +COUNTIF([Data 9]@row, <>"..."), +COUNTIF([Data 10]@row, <>"..."))

Then a simple formula to find the # of blank columns (it's assuming you've got 10 columns you were checking to see if were blank):

=SUM(10 - [Primary Column]@row)

Then a column formula to find the % blank:

=SUM([Blank Count]@row / 10)

• Options

Thank you everyone. This was really helpful and very appreciated.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!