Excel - Count Blank Rows to Next Value

[mrubel99]

I am trying to come up with a formula that from a value say in A1, will count itself and the number of blank rows, which will vary between values and change on a daily basis, to the next value in column A and put the result on the same row as the value (blue values in example data).

Thanks, any help is greatly appreciated in advance and please let me know if I need to provide additional information.

[JayF]

This isn't the most beautiful solution, sorry, but it works.

Hopefully someone superior to myself can come up with a better solution for you, there probably is one.

[JayF]

In case you're still checking back.

I was just thinking about this again. A better way to do it would be to have the same reference number in all the blank cells. You could then use a pivot table to summarise the list. This would also give you the ability to analyse the data far more effectively.

It also means you'll need a simple formula to count the instances:

=IF(A2=A1,"",CO UNTIF(A:A,A2))

See the attached sample.