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

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

Find more posts tagged with

Accepted answers

Mike TV

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)

All comments

James Keuning

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)

That's it. That is your denominator for your percentage calculation.

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.

Mike TV

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):

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)

Dax67

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.