Count non-blank cells after a blank cell

[Pratap 1987]

Hi there,

I need assitance in building a formula or vba which could pull me the count of non-blank cell after the blank cell.

Please note that we could have multiple blank cells in the same row. The formula or vba should pull the count of non-blank cell only after previous blank cell.
Example:
(Note: in the below example Row # 3 has 2 blank cells. When it finds the blank cells it should locate the last or previous blank cell - in the example from Column F instead of Column C)

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]Count[/TD]
[/TR]
[TR]
[TD="align: center"]100[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"][/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]350[/TD]
[TD="align: center"]450[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]100[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"][/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]100[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"][/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"][/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]1[/TD]
[/TR]
</TBODY>[/TABLE]

 

[lrobbo314]

How about this

=COUNTA(INDIRECT(ADDRESS(ROW(A2),MAX(IF(A2:G2="",COLUMN(A2:G2),""))+1,1,TRUE)&":G"&ROW(A2)))

Adjust your ranges and hit Control + Shift + Enter after typing in the formula.

 

[Pratap 1987]

Awesome.... You are the best!!! Thank you very much.

 

[XOR LX]

Also, CSE:

=COLUMNS(A2:G2)-MATCH(1,0/(A2:G2=""))

Regards

 

[lrobbo314]

Thanks Pratap.

Nice formula XOR. So, the match function will subtract the LAST blank cell from 7? Because I have an example with 2 blank cells, 1 in column C and 1 in column F. the Match seems to ignore the first one and return the last. Is that the way match works in general?

 

[XOR LX]

Nice formula XOR.

Cheers!

Is that the way match works in general?

Not in general, no. Only in this use: if the lookup_value is not found within the lookup_array, then MATCH, with a parameter of 1 (or, equivalently, omitted) returns the position of the last numerical value within the lookup_array which is less than the lookup_value.

In this construction, since a clause such as:

0/(A2:G2="")

can only ever consist of either zeroes or #DIV/0! errors by virtue of the fact that:

A2:G2=""

will return an array of Boolean TRUE/FALSE returns, and 0/TRUE=0 and 0/FALSE=#DIV/0!, we can be certain that a lookup_value of 1 will be sufficiently large.

Hence, the position of the last numerical value within our array (which, as I said, will be a zero) will be returned.

Regards