# Count empty numbers and cells

#### deciog

##### Active Member
In VBA only formulates Excel 2016 version and also for version 365

I need to count sequence of numbers and also sequence of empty cells

Thank you very much in advance

Decio

#### Attachments

• 11.4 KB Views: 6

#### Fluff13

##### Active Member
CSS:
``````Sub deciog()
Dim Cl As Range
Dim i As Long

For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
With Cl.Resize(, 25).SpecialCells(xlConstants).Areas
For i = 1 To .Count
Cl.Offset(, 25 + i).Value = .Item(i).Count
Next i
End With
With Cl.Resize(, 25).SpecialCells(xlBlanks).Areas
For i = 1 To .Count
Cl.Offset(, 38 + i).Value = .Item(i).Count
Next i
End With
Next Cl
End Sub``````

#### Peter Bartholomew

##### Well-Known Member
@deciog
Good call for the VBA!
Naturally I wanted to solve the problem with a dynamic array but it was not easy! I used the beta release function LET to organise the calculation and a function ACCUMULATE, written by Charles Williams to address the problem of DA not supporting breakup of array formulae. The function is intended to turn cash flows into balances and allows for variable interest rates (I have used -100% to zero an accumulation step).
Code:
``````= LET(
sign, SIGN(sequence),
consecutive, ACCUMULATE(sign, 0, sign - 1),
step, DIFF(consecutive, ,-1) + (k=25)*consecutive,
FILTER(step, step>0) )``````
1. The process consists of flagging numbers by 1
2. Accumulate 1s until a 0 is found
3. Compare consecutive numbers to identify the steps
4. Correction to ensure final value is included
5. Filter to give the remaining positive counts

#### bosco_yip

##### Excel Ninja
Another formula option

1] "Number Sequence", in C8 CSE formula copied across right and down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TEXT(FREQUENCY(IF(\$C2:\$AA2<>"",\$C\$1:\$AA\$1),IF(\$C2:\$AA2="",\$C\$1:\$AA\$1)),"0;;;"))&"</b></a>","//b["&COLUMN(A1)&"]"),"")

2] "Space Sequence", in C14 CSE formula copied across right and down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TEXT(FREQUENCY(IF(\$C2:\$AA2="",\$C\$1:\$AA\$1),IF(\$C2:\$AA2<>"",\$C\$1:\$AA\$1)),"0;;;"))&"</b></a>","//b["&COLUMN(A1)&"]"),"")

#### Attachments

• 16.6 KB Views: 1
Last edited:

#### shrivallabha

##### Excel Ninja
If TEXTJOIN function is available to you then you can try below formula as well.

In cell AC2:
=LEN(TRIM(MID(SUBSTITUTE(" "&TRIM(TEXTJOIN("",TRUE,IF(\$C2:\$AA2<>"",1," ")))," ",REPT(" ",99)),99*COLUMNS(\$A\$1:A\$1),99)))
Copy down and across.

In cell AP2:
=LEN(TRIM(MID(SUBSTITUTE(" "&TRIM(TEXTJOIN("",TRUE,IF(\$C2:\$AA2="",1," ")))," ",REPT(" ",99)),99*COLUMNS(\$A\$1:A\$1),99)))
Copy down and across.

Both are essentially the same formulas except for the slight change (marked in red). Blank cells will be marked with 0.

#### deciog

##### Active Member
Fluff13, good morning.

It worked perfectly thank you very much.

Peter, good morning.

In my version Microsoft 365 does not have this function ACCUMULATE and DIFF.

bosco_yip, Good morning.

It worked perfectly thank you very much.

shrivallabha, good morning.

Perfect , it worked very well thanks.

Decio

Last edited:

#### Fluff13

##### Active Member
You're welcome & thanks for the feedback.

#### Peter Bartholomew

##### Well-Known Member
@deciog
Yes, I didn't post the workbook because it wouldn't work on other 365 installations. I may choose only to develop for MS365 these days but to rely on Charles Williams's FastExcel is taking it too far.
I struggled to find a dynamic array solution, but then realised that I was accumulating ones (otherwise known as 'counting'!)
The latest attempt filters to find the position of first and last numbers and subtracts.

It may be possible to follow this idea and further simplify; after all,
isNumber*precededBySpace? is closely related to isSpace*followedByNumber?

#### Attachments

• 12.4 KB Views: 1

#### deciog

##### Active Member
Peter

Ecelente worked perfectly thank you very much

Decio