Count Blanks in dynamic range

[mgirvin]

Dear Smartest Excelers Around,

I want to count blanks in a horizontal range (all in one row) that will change (dynamic range). The values in the range could be numbers or words. Some values may be added to the end, but there may still be some empty cells to the right of the last value. My goal is to count blanks in the range up to the last entered value, but no beyond that. As an example:

A6 = 2
B6 = empty cell
C6 = 2
D6 = empty cell
E6 = tt
F6 = empty cell

The range for the count blanks would be A6:E6. F6 is not included because the last entered value is in cell E6.

The answer (count blanks in dynamic range) should be 2.

I have got these 3 formulas to work, but it seems that there must be a better (shorter, faster calculating, more elegant) formula than these:

=COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6)))))))

=COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+{1,2,3,4,5,6})))

=COUNTBLANK(A6:INDEX(A6:F6,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6)))))))

Any ideas?

P.S. The trouble I was having with creating a formula was that all the blanks became zeros when I started analyzing it with MATCH and other functions...

 

[mgirvin]

Matty,

Thanks for taking the time to help me. Yes, you are right about Domenic, I have been getting EXCELlent help from him for years!!

 

[Aladin Akyurek]

Assuming that the count must be done with COUNTBLANK (which does not skip formula blanks)...

Control+shift+enter, not just enter...

One of:

=COUNTBLANK(OFFSET(A6,0,0,,MATCH(2,1/(6:6<>""))-MIN(COLUMN(A6))))

=COUNTBLANK(A6:INDEX(6:6,MATCH(2,1/(6:6<>""))))

 

[Domenic]

Thanks, Matty, EB08 and Domenic!

You're very welcome!

Domenic, I clicked on the link in your signature line (xl-central.com). Is that a web site that you created?

Yes it is... Thanks for checking it out.

 

[krn6264]

Wow, these are some great formulas clean and precise. If I wanted this to turn around and count blanks down rows instead what would need to change?


=COUNTIF(INDEX(A6:F6,MATCH(TRUE,A6:F6<>"",0)):INDEX(A6:F6,MATCH(2,1/(A6:F6<>""))),"")

I tried

=countif(index(F6:F12,,MATCH(TRUE,F6:F12<>"",0)):INDEX(F6:F12,,MATCH(2,1/(F6:F12<>"")))."") <Ctrl Shift Enter> and got a REF error.

I thought by changing the index to look at column versus row that would work...

 

[mgirvin]

krn6264,

Try:

For counting blanks between the first cell and the last cell that contains a value, try:

=COUNTIF(F6:INDEX(F6:F12,MATCH(2,1/(F6:F12<>""))),"")
or
=COUNTBLANK(F6:INDEX(F6:F12,MATCH(2,1/(F6:F12<>""))))


For counting blanks between the first cell that contains a value and the last cell that contains a value, try:

=COUNTIF(INDEX(F6:F12,MATCH(TRUE,F6:F12<>"",0)):INDEX(F6:F12,MATCH(2,1/(F6:F12<>""))),"")
or
=COUNTBLANK(INDEX(F6:F12,MATCH(TRUE,F6:F12<>"",0)):INDEX(F6:F12,MATCH(2,1/(F6:F12<>""))))

Enter each formula with the key strokes Ctrl + Shift + Enter.

 

[krn6264]

Thanks Mike

I used the first countif and extended out my range to worst case. Counting blanks just great. Thanks so much for the expertise.

 

[mgirvin]

Matty and Domenic provided the expertise!

By The Way, it looks like your original formula was correct except for the dot between the two COUNTIF arguments.

 

[krn6264]

ha, didn't even notice that. Thanks to Domenic, Matty, and Mike (for presenting question so eloquently for me).

Special thanks to Mike for all the great videos on his YouTube channel. I have learned COUNT less things from him.

 

[President]

Assuming that the count must be done with COUNTBLANK (which does not skip formula blanks)...

Control+shift+enter, not just enter...

One of:

=COUNTBLANK(OFFSET(A6,0,0,,MATCH(2,1/(6:6<>""))-MIN(COLUMN(A6))))

=COUNTBLANK(A6:INDEX(6:6,MATCH(2,1/(6:6<>""))))

you are amazing Aladin