Clever Geek Handbook

Right to Left Match the number of nonblank cells

How to change this formula:

=MATCH(1,--(AA1:AH1=""),0)-1

count from right to left? I want to count the number of non-blank cells to an empty cell, but I need to do this starting from right to left.

+3


source to share


2 answers


Array formula (validated with ctrl+ shift+ enter)

=MAX(IF(LEN(AA1:AH1)<>0,COLUMN(AA1:AH1)))

must specify the index of the column containing the last nonblank cell, even in a row with multiple intercalated blank cells.

Array formula (validated with ctrl+ shift+ enter)

=MAX(IF(LEN(AA1:AH1)=0,COLUMN(AA1:AH1)))

must specify the index of the column containing the last blank cell, even in a row with multiple intercalated blank cells.



Then you can use these values ​​to calculate the results you want.

those.: example table to this topic attempt

Combine it all:

=MAX(IF(LEN(AA1:AH1)<>0,COLUMN(AA1:AH1))) - MAX(IF(LEN(AA1:AH1)=0,COLUMN(AA1:AH1)))

should give you the output you want.

+1


source


I don't know how to do this using the match () function, but you can do it differently: Ctrl + Shift + Enter:

=8-MAX(IF(AA1:AG1="",COLUMN(AA1:AG1)-26))


Thank,

0


source






More articles:


All Articles