Counting Question

[AllenB]

I am looking for a formula that tells me how many consecutive matches have just occurred and hence the count for them. The Countif I know will only tell me how many out of how many. I did this once with a long series of columns with "Ifs" but lost it.
Is there a simple formula for this?

 

[Cartref45]

AllenB,

If you want to know repeats by position you can use the "SUMPRODUCT" Function else if it's just the number of repeats regardless of position you can use the "COUNTIF" Function.

If your draw numbers are in Columns A:F (5/39) lottery for example and the latest draw is in Row 1.

=SUMPRODUCT(--(A1:F1=A2:F2)) this will give you positional repeats.

=COUNTIF(A2:F2,A1)+COUNTIF(A2:F2,B1)+COUNTIF(A2:F2,C1)+COUNTIF(A2:F2,D1)+COUNTIF(A2:F2,E1) this will give the number of repeats regardless of position.

There are probably more knowledgeable Excel experts on the forum that might know an Array Formula that will do the second option easier.

Rgds

Cartref

 

[Icewynd]

I just create a separate column for this -- zero if no match, add one to previous value if it is a match.

 

[AllenB]

Thank you. I will study the SumProduct function

 

[AllenB]

I remember that process now. Great for illustrating runs.
Here I was only looking at the end of the series. For example a A Double in Pick 3. I see now that I could use the match function to search back for a Single from the end of the line. If the end is a single then the match would look for a Double.
I don't think I can add to much more to some of the Monsters I am working on.