# Counting max number of blanks between cells in a dynamic range

#### HTX_Excel_Dude

##### New Member
Hello Excel Wizards!

I'm looking for an excel formula that will count the maximum number of blanks (formula blanks of "") between any 2 values in a row. The row will contain many different values, and are dynamic. I have thousands of rows where the values will be in different columns.

Example:

 Value1 Value2 Value3 Value4 Value5

So essentially I am looking for a formula that would return the number '3', because there are 3 blanks between 'Value3' and 'Value4', compared against 1 blank between 'Value2' and 'Value3', and also against 1 blank between 'Value4' and 'Value5'.

The blanks are not truly empty cells, but instead contain "" derived from a formula within that cell.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+Fluff v2.xlsm
16191618519518161610518812197101716
2???? ??????????  ??????  ???????? 2
Main
Cell Formulas
RangeFormula
L2:AE2L2=IF(MOD(L1,4)=0,"","??")
AG2AG2=MAX(FREQUENCY(IF(L2:AE2="",COLUMN(L2:AE2)),IF(L2:AE2="",0,COLUMN(L2:AE2))))

Ignore the formula in L2:AE2

#### HTX_Excel_Dude

##### New Member
Quite brilliant! Thank you!

My only problem is that it's counting the blanks at the end of my array. My row doesn't necessarily end with a value. Any adjustment I can make to this formula?

#### Fluff

##### MrExcel MVP, Moderator
Do you have the LET function yet?

#### HTX_Excel_Dude

##### New Member

Not yet unfortunately.

#### Fluff

##### MrExcel MVP, Moderator
Not a problem, just means the formula is a bit longer.
+Fluff v2.xlsm
1619161851951816161051881219716816
2???? ??????????  ??????  ????   2
Main
Cell Formulas
RangeFormula
L2:AE2L2=IF(MOD(L1,4)=0,"","??")
AG2AG2=MAX(FREQUENCY(IF(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1))="",COLUMN(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1)))),IF(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1))="",0,COLUMN(L2:INDEX(L2:AE2,XMATCH(1,--(L2:AE2<>""),0,-1))))))

#### HTX_Excel_Dude

##### New Member

Sorry to be a pain, but I don't have access to XMATCH either. Can I use a variation of MATCH instead?

#### Fluff

##### MrExcel MVP, Moderator
In that case I'm not sure how to do it.
Hopefully somebody who's better with formulae will step in.

#### Peter_SSs

##### MrExcel MVP, Moderator
I presume that means you don't have the FILTER() function either?
Assuming you don't, AG2 below is a direct adaptation of Fluff's formula using functions available in earlier Excel versions.
However, I would seriously consider using a helper cell like column AI (that column could be hidden) which then makes the column AH formula much more readable/manageable.

Note too though that Fluff's original formula and theses do count blanks at the start of the range (see row 3). If that is a problem then I would introduce another helper to locate the first non-blank column and adjust the the column AH formula to use that value as well. Post back if you need this.

20 11 12.xlsm
167161851951816161051881219716816
2???? ??????????  ??????  ????   2217
3   ??????????  ??????  ??    3316
Count Blanks
Cell Formulas
RangeFormula
L2:AE2L2=IF(MOD(L1,4)=0,"","??")
L3:AE3L3=IF(OR(MOD(L1,4)=0,L1=6,L1=7),"","??")
AG2:AG3AG2=MAX(FREQUENCY(IF(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1))="",COLUMN(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1)))),IF(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1))="",0,COLUMN(L2:INDEX(L2:AE2,LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1))))))
AH2:AH3AH2=MAX(FREQUENCY(IF(L2:INDEX(L2:AE2,AI2)="",COLUMN(L2:INDEX(L2:AE2,AI2))),IF(L2:INDEX(L2:AE2, AI2)="",0,COLUMN(L2:INDEX(L2:AE2,AI2)))))
AI2:AI3AI2=LOOKUP(2,1/(L2:AE2<>""),COLUMN(L2:AE2)-COLUMN(L2)+1)

Replies
1
Views
150
Replies
2
Views
135
Replies
3
Views
270
Replies
7
Views
129
Replies
3
Views
69

1,127,554
Messages
5,625,471
Members
416,108
Latest member
Ravenhold

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back