Counting number of consecutive blank cells on adjacent rows

[johnnybj]

Hi,

I have found several solutions in here on how to count the highest number of consecutive blank cells in a single row, like so;

=LARGE(FREQUENCY(IF(A10:N10<>"","",COLUMN(A10:N10)),IF(A10:N10="","",COLUMN(A10:N10))),1)

This works like a charm. However, how do I go about counting the (highest) number of consecutive blank cells in two adjacent rows?

Let's assume that the two adjacent rows are 9 and 10. Let's also assume that the last 3 cells in row 9 are empty and that the 4 first cells in row 10 are empty as well.
The function should count that as 7 consecutive empty cells.

(Btw, I have tried changing the range used above from A10:N10 to A9:N10 to no avail)

Thanks in advance...!!!

John

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe this array formula...
=MAX(FREQUENCY(IF(A9:N10="",ROW(A9:N10)*10^5+COLUMN(A9:N10)),IF(A9:N10<>"",ROW(A9:N10)*10^5+COLUMN(A9:N10),"")))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[johnnybj]

Thanks a lot Marcelo..!!!

You're a lifesaver! This works perfect, and I really appreciate that you were able to come up with a solution so fast..!!
I have been programming computers for 30 years, but I realize that I'm a complete novice when it comes to some of the advanced Excel functions..

Best Regards,
John

 

[johnnybj]

Hi again Marcel (or anyone else who cares to answer).


How about if the two rows are not adjacent? Let's say that the rows in question are 8 and 10? (row 9 to be ignored).


Thanks in Advance!!


John

 

[Marcelo Branco]

Maybe...

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(A8:N10),{8;10},0)),IF(A8:N10="",ROW(A8:N10)*10^5+COLUMN(A8:N10))),IF(ISNUMBER(MATCH(ROW(A8:N10),{8;10},0)),IF(A8:N10<>"",ROW(A8:N10)*10^5+COLUMN(A8:N10)))))
Ctrl+Shift+Enter

Assumes data in A8:N8 and A10:N10

M.

 

[johnnybj]

Hi Marcelo,

Absolutely frigging amazing..!!!!! It's working as intended.
Thanks a lot for your help! It's priceless...

Best Regards,
John