Count adyacent non-blank cells in Excel/Google Sheets-CodePudding

I have an Excel/Google Sheets table in which some rows contain blank cells in between non-blank cells. I would like to count from left to right. I have used the formula COUNTA(A2:F2) but it cannot achieve what I want. This is a sample of the outcome I would like to get, with the Personalized count that I am seeking to achieve and the classic COUNTA:

CodePudding user response：

I think that the task is slightly different from what you describe in the question, you want to count until blank and not between not blank cells.

So I made this working example

 --- ------ ------ ------ ------ ------ ------ ------- 
|   |  A   |  B   |  C   |  D   |  E   |  F   |   G   |
 --- ------ ------ ------ ------ ------ ------ ------- 
| 1 | 2021 | 2020 | 2019 | 2018 | 2017 | 2016 | Count |
| 2 |    1 |    1 |    1 |    1 |    1 |    1 | 6     |
| 3 |    1 |    1 |    1 |    1 |      |      | 4     |
| 4 |    1 |      |    1 |    1 |    1 |    1 | 1     |
| 5 |    1 |    1 |    1 |      |      |    1 | 3     |
 --- ------ ------ ------ ------ ------ ------ -------

where cell G2 contains the following:

=IFERROR(MATCH(1;--(A2:F2="");0)-1;COUNTA(A2:F2))

is it right for you?