Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Counting non blank cells in a row till first non blank.
I need to count blank cells in a row, left to right, till first non blank cell. the non blank cell may contain any character/s or digits. Any suggestions?
For instance this formula will work to count blank cells till it finds X in row 1, columns 120, but not anything else: =MATCH("x", [Column1]1:[Culumn20]1, 0)  1
Comments

I'm not certain that this what you're looking for, but you can create a COUNTIF formula that spans the horizontal range of your cells for anything that is blank, such as the following example:
=COUNTIF([Task Name]1:[Assigned To]1, "")
The above formula will span the range of a row (in my sheet, my first Column is titled Task Name and my last column is titled Assigned To) and will count cells that are blank. If you add columns to the ends of the sheet, you'll need to adjust the formula range to include those columns.

Thanks Shaine
That will then count ALL the blanks in the row. I wish to count from left to right till the FIRST non blank, then ignore any further blanks to the right of the first non blank.
In other words, in this representation below the answer would be 6. That is 5 blanks till the X in column 7. My formula above will do that, but only for X. If there is another character there it does not work.
Columns: 1 2 3 4 5 6 7 8 9 10
Row 1: X A

Oh! Sorry for misunderstanding. There isn't a way to perform this action in Smartsheet, as Smartsheet isn't able to understand how to stop iterating a formula after finding the first cell that meets the formula conditions. (We also don't have looping functionality such as "count if blank until the first nonblank.")
I'll pass your feedback for this to our Product team for further review.

HGS,
I'm getting some very odd results with the MATCH() function, so I did not get the solution I was looking for.
What I did, however, was add a row with this formula in each cell:
=IF(ISBLANK([Column1]23), "", "x") + ""
where this is showing the formula in column [Column1] and row 23.
I then used your match funtion to find the "x"  which is the first nonblank column.
Hope this helps.
Craig
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives