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.

Options
HGS
HGS
edited 12/09/19 in Archived 2016 Posts

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

Comments

  • Shaine Greenwood
    Options

    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
    Options

    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
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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.

     

    Hope this helps.

     

    Craig

     

This discussion has been closed.