Google Sheets - Find Consecutive Non Blanks-CodePudding

Hopefully quite a simple query.

I need to know if there are any consecutive non-blanks in each row.

EG: Row 1 and Row 3 do, but row 2 does not.

CodePudding user response：

Give a try on below formula-

=ArrayFormula(IF(ISNUMBER(SEARCH("11",JOIN("",IF(A1:F1<>"",1,0)))),"Has consecutive","Hasn't consecutive"))

CodePudding user response：

The first option, I think you can use a canditional formatting, with a custom formula. Like if A1 and A2 are not empty turn them red, if they are empty turn them green.

Another option:

First, you need to decide which of the 2 options you will use (COUNTA or SUMPRODUCT,) and use an if to count the 2 cells if they are not blank.

For example:

If A1 and A2 are not blank, then you count them, on the next row, if A2 and A3 are not blank count them.

Count Cells If Not Blank Using COUNTA Function

This is an example of how to use COUNTA:

=COUNTA(A2:A3)

However, and mentioned in the document. If there is a especial character in the cell, even if it looks empty it will be counted with that formula.

Count Non-Empty Cells Using SUMPRODUCT Function

This is an example of how to use SUMPRODUCT:

=SUMPRODUCT(LEN(TRIM(A2:A3))&gt;0)