# Count blank cells in a range and contiguous blank cells also as single cells! Tricky One!

#### SebastianColombia

##### New Member
I have a tricky need in Excel, which I haven't been able to solve. I think it's quite complicated and I don't have enough knowledge about VBA codes.
. I would really appreciate if you could help me with this.!!!!!!

Basically what I'd like to do is to create a function. The input would be a RANGE with blank cells, and non blank cells. I would expect the function to COUNT 'SINGLE BLANK CELLS' as single "ranges" and CONTIGUOUS BLANK CELLS as single ranges too. I know it's kind of tricky, but I made a video.

Thanks a lot.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
 Row\Col A​ B​ C​ D​ 2​ 2​ 4​ 3​ 4​ jad 5​ 6​ 7​ 8​ nad 9​ 8​ 10​ 11​ 5​ 12​ 3​ 13​ 14​ 15​ 6​

D1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A15="",ROW(A2:A15)),IF(1-(A2:A15=""),ROW(A2:A15))),1))

Thank you very much sir!!! Does this work just for rows???

Thanks a lot!!

Thank you very much sir!!! Does this work just for rows???

Thanks a lot!!

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:Z2="",COLUMN(A2:Z2)),IF(1-(A2:Z2=""),Column(A2:Z2))),1))

should also work, if this is what you meant...

Replies
4
Views
292
Replies
0
Views
394
Replies
17
Views
429
Replies
4
Views
687
Replies
3
Views
290

1,214,323
Messages
6,118,890
Members
448,854
Latest member
Eduard_Stoo

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