counting contiguous cells

Brass Contributor

I need to count the contiguous cells on a row, to the right of a cell.  Any way to do that (even though there may be other cells beyond it, and I won't have a firm number on how many that is at any given time).

 

Thanks much.

6 Replies
You may try this formula in B2:
=COUNTA(C2:INDEX(2:2,COUNTA(2:2)))

@Twifoo, thanks (for your several responses), what if I don't know that it's row 2 in which I need this?  This will go into varying rows, and those rows may shift over time with insertion above it, so I need that part of the formula to self-adjust with it.  How would I alter this to accommodate for that?

 

Thanks again.

All references in the formula are relative so that you can copy and paste it anywhere and it will return the count of data cells to the right of the cell where it’s entered.

@Twifoo, I think my original request must have not been clear enough. Attached example shows the two shaded cells, into which I need a formula (which I can copy into other cells of similar nature), where the immediately contiguous cells to the right of the formula, are the ones I need to count.

 

Hope that's now clearer.

The formula in B2 is:
=MATCH(1,
INDEX(--(C2:$XFD2=""),0),
0)-1

@Twifoo, thanks.  Would love to understand what the formula is doing, inside to out.  I'm not exactly following some of the pieces.  Appreciate the extra explanation.