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 1-20, but not anything else: =MATCH("x", [Column1]1:[Culumn20]1, 0) - 1

Find more posts tagged with

API and Developers

Finance

IT & Operations

Account and User Management

Comments

Shaine Greenwood

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.

HGS

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

Shaine Greenwood

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 non-blank.")

I'll pass your feedback for this to our Product team for further review.

J. Craig Williams

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 non-blank column.