Count empty spaces between entries

[ccpsc]

I need to count the number of empty spaces between entries in column A and display them as shown in column C. I have spent a lot of time trying to work out how to do this with no luck so far. Can any help?

[AliGW]

In C1 copied down:

=IF(A1="","",IFERROR(MATCH(1,SIGN(A2:$A36),0)+IF(ROW()=1,0,1),MATCH(99^99,B:B)-ROW()+1))

[Glenn Kennedy]

Does that give the expected answer??

maybe this:

=IF(A1<>"",IFERROR(INDEX(ROW($A2:$A$36),MATCH(TRUE,INDEX(($A2:$A$36<>0),0),0)),1+LOOKUP(2,1/($B$1:$B$36<>""),ROW(B$1:$B$36)))-ROW(),"")

[kvsrinivasamurthy]

Try this. In C1 then copy down.

=IF(A1="","",IFERROR(AGGREGATE(15,6,ROW($A2:$A100)/($A2:$A100<>""),1)-ROW($A1),COUNTA($B1:$B$100)))

[ccpsc]

Thank you all for replying, I appreciate it. AliGW, that didn't work it adds the results together from bottom to top. Glenn Kennedy and kvsrinivasamurthy, thank you both formulas work and have solved my problem.

[AliGW]

It gave the same results as your sample file when I put it together this morning.

[ccpsc]

I just tested it again and it gives the result from top to bottom, 36, 27, 21 and 14. It should be 9, 7, 7, 14. See Example 2 attached.

[AliGW]

As I said, it was working this morning. I can’t test it now - I will look again tomorrow.

[AliGW]

Yes, it's working here (in column C), but I have a feeling it's because I have the insider version of Office 365. I see things like this sometimes with array formulae, so all I can suggest is that you try entering it with CTRL+SHIFT+ENTER.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	15/07/19	4559	9	9	IF(A1="","",IFERROR(MATCH(1,SIGN(A2:$A36),0)+IF(ROW()=1,0,1),MATCH(99^99,B:B)-ROW()+1))
2		4558
3		4557
4		4556
5		4555
6		4554
7		4553
8		4552
9		4551
10	13/07/19	4550	7	7
11		4549
12		4548
13		4547
14		4546
15		4545
16	12/07/19	4544	8	7
17		4543
18		4542
19		4541
20		4540
21		4539
22		4538
23	11/07/19	4537	14	14
24		4536
25		4535
26		4534

Sheet: Sheet1

It should be 9, 7, 7, 14

The third value is incorrect.

[AliGW]

Having reviewed my workbook, I realise that the formula was slightly off - it was adding one to all values except the first and last.

This will give the correct results:

=IF(A1="","",IFERROR(MATCH(1,SIGN(A2:$A36),0),MATCH(99^99,B:B)-ROW()+1))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[ccpsc]

Hi AliGW, yes that formula works, thanks.

[Phuocam]

Or try this the formula in C1 and copy down:

=IF(A1="","",COUNTA(B1:$B$37)-SUM(C2:$C$37))

[AliGW]

Very slick - and obvious when you see it!!!