Formula Help

I'm trying to do something with excel and I'm not quite sure it's possible.


I have data on a spread sheet ranging from 1:15


One step I have taken in what I want to do is having a formula to count the number of time a certain data input shows up:


=COUNTIFS('spread sheet'!1:15,"data1")


But what I want to do now is have excel take all the variances where the value of "data1" is true in a cell and then count the number of times "data2" shows up in the cell below it... so that it will count the number of times 1 is followed by 2 (or 3 or 4 etc).


Example:


data1 data1 data2 data3 data1
data3 data2 data1 data4 data2
data2 data1 data3 data1 data4
data1 data3 data4 data3 data3
data3 data1 data2 data4 data1
data1 data4 data1 data3 data2


So if I used =COUNTIFS('spread sheet'!1:15,"data1") I'd get a value of 11


What I want is a formula that will find each "data1" and then look beneath it to see if the cell below it contains "data2"... in this case it should return a value of 3


Is that possible?

Kinda crude but it gets it done.

Using Tommy's data sample, you could use an array formula such as:


Remember to use Ctrl-Shift-Enter to make the formula work as an array formula.

Cheers,