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

[SebastianColombia]

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.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

2​

[/td][td]

2​

[/td][td][/td][td][/td][td]

4​

[/td][/tr]

[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]jad[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]nad[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

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

 

[SebastianColombia]

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

Thanks a lot!!

 

[Aladin Akyurek]

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