Counting number of consecutive blank cells on adjacent rows

johnnybj

New Member
Joined
Feb 18, 2019
Messages
4
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

New Member
Joined
Feb 18, 2019
Messages
4
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

New Member
Joined
Feb 18, 2019
Messages
4
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

New Member
Joined
Feb 18, 2019
Messages
4
Hi Marcelo,

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

Best Regards,
John
 

Watch MrExcel Video

Forum statistics

Threads
1,108,633
Messages
5,523,995
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top