Count Quantity of Specific Number Patterns in Column

[KennyT]

Hi. First time posting a help request. Spent endless hours trying to get this to work, but to no avail. Maybe someone can assist.

Need to know the formula for counting how many times a certain number follows another given number in the same column. Per my example, how many times does the number '5' follow the number '100'? The answer should be '3'. In the actual spreadsheet, the number combos will vary (it is not always these two numbers). It is possible that numbers will be greater or less than the one preceding it. I assume one might use "COUNTIF", but having trouble getting the formula to focus on one row below a particular number.

Thanks for your help in advance!

100
5
27
5
13
100
5
100
10
5
100
5
26

 

[Aladin Akyurek]

Try...

E2: 100
F2: 5

G2:

=SUMPRODUCT(--(A2:A13=E2),--(A3:A14=F2))

 

[KennyT]

It works! I am not sure how that formula achieves the goal, but it seems to do the job. I will apply it to the full spread sheet to test it out in the "real world."

Your time is greatly appreciated. Thanks so much! You are a genius!

 

[Aladin Akyurek]

It works! I am not sure how that formula achieves the goal, but it seems to do the job. I will apply it to the full spread sheet to test it out in the "real world."

What it does is:

A2 = E2 and A3 = F2 --> 1, otherwise 0;
A3 = E2 and A4 = F2 --> 1, otherwise 0; And so on.

Your time is greatly appreciated. Thanks so much! You are a genius!

You are welcome. Thanks for providing feedback.