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
Best Answer
-
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)
Answers
-
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.
-
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)
-
Thank you everyone. This was really helpful and very appreciated.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!