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

SebastianColombia

New Member
Joined
Oct 21, 2013
Messages
12
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.

https://www.youtube.com/watch?v=QGS8DBcA5HA&feature=youtu.be

Thanks a lot.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,821
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top