Count number of blank cells in row between last ce

问题:

Is it possible (with a formula preferably) to count the number of blank cells in row, where the counting starts at a given column and counts going backward (e.g. right to left) the number of blank cells until a non-blank cell is found? In the example below, the counting begins at Column H and proceeds leftward. Using COUNTA or COUNTIF seem like reasonable tools to use, but I am unsure on how to terminate the counting once a non-blank cell is found.

回答1:

You can use something like this if the values in your table are all text:

=COUNTBLANK(INDIRECT(CHAR(97+MATCH("zzzz",B2:H2))&ROW()&":H"&ROW()))

MATCH("zzzz",B2:H2) returns the column number in which the last non-blank cell is.

CHAR(97+ column number) returns the letter of that column.

Append it to the row number to give the reference where the COUNTBLANK has to start with &ROW()

&":H"&ROW()) gives the reference of the last cell, which is H plus the row number.

INDIRECT turns the concatenated text into a range that Excel can evaluate.

回答2:

Try this formula

=COLUMNS(B2:H2)-MATCH("zzzz",B2:H2)

回答3:

You could use nested if statements

=IF(ISBLANK(H2),IF(ISBLANK(G2),IF(ISBLANK(F2),IF(ISBLANK(E2),IF(ISBLANK(D2),IF(ISBLANK(C2),IF(ISBLANK(B2),IF(ISBLANK(A2),8,7),6),5),4),3),2),1),0)