# Counting number of consecutive blank cells on adjacent rows

#### johnnybj

##### New Member
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)

John

### 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
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
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
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).

John

#### Marcelo Branco

##### MrExcel MVP
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
Hi Marcelo,

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

Best Regards,
John

Replies
3
Views
32
Replies
1
Views
63
Replies
1
Views
55
Replies
3
Views
73
Replies
5
Views
46