Count the gaps between two consecutive data

Still with 2019?

If you have 365, this will work:

=LET(d,FILTER(ROW(A1:A23),A1:A23),t,TAKE(d,-(ROWS(d)-1)),f,TAKE(d,ROWS(d)-1),(t-f)-1)

Thanks but Ma'am it's not working in my excel.

I asked if you still hd 2019 - you did not respond. Do you?

I also said that the solution would work if you had 365.

In older Excel versions, please try

Formula:

Please Login or Register  to view this content.

This formula returns the gaps including the number of FALSE's before the first TRUE and the number of FALSE's after the last TRUE.

See post #7

Originally Posted by Blacktiger1

I am extremely. I could not see your message at that time.

Well, it was there. It seems that you just aren't reading all of the messages in the thread - please do so.

Then, if that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Thanks mate. It worked. Here is something which is troubling me again. I am attaching spreadsheet.

what I want to do is - Count a certain pair of numbers in column E. Pair I want to count is 5,0...such that Zero appears immediately after 5 in Column E.

E9 5
E10 0

I want to count this pair in the list given in Column E. ( I have uploaded the spreadsheet)

Thanks in Advance.