Excel Formula - How Many Times a Value Appears

[ststern45]

Hi everyone,

Have a request for a formula.

In cell A10 I have a value of 1

Cell A15 I would have the formula that would do the following:

I know the value 1 is in cell A20. I want to count how many times the value 1 repeated below cell A20 before the value changed. So in the example below the total or result would be 2 since the 1 appeared in cell A20 and also A21 before the value 0 appeared in cell A22.
So in simple terms how many times did a value repeat based on what the value is in A20 only.


Cell range A20 through A30 contain the following values

A20 = 1
A21 = 1
A22 = 0
A23 = 0
A24 = 0
A25 = 1
A26 = 2
A27 = 0
A28 = 1
A29 = 0
A30 = 1

Thanks in advance!!

 

[Fluff]

How about
=MATCH(TRUE,(A20:A30<>A20),0)-1

This needs to be confirmed with Ctrl Shift Enter

 

[Snakehips]

I was thinking similar but needs the reference to A10 ??

=MATCH(FALSE,A20:A1000=A10,0)-1

Again Ctrl+Shift + Enter

Hope that helps.

 

[Fluff]

Wasn't sure if it should be A10 or A20, I went with A20 based on this

based on what the value is in A20 only

We'll just have to wait & see

 

[ststern45]

Thanks the A10 contained the most recent value = 1
I only tried the =MATCH(FALSE,A20:A1000=A10,0)-1 and worked to perfection.

Sorry about the confusion. I did change the cell to A20 instead of A10

Thank you all!!

 

[Fluff]

Glad we could help & thanks for the feedback