In Excel how to do a pairing of A,B,C in form of A

问题:

I need a help to do below thing. Suppose I have column A which contains values either of A,B or C as Follows.

Column A
A
A
B
A
B
B
C
A

Now what I have to do is, I want to count pair of AA, AB, AC likewise, based on the two consecutive values, if First row contains A and 2nd row contains A, then AA count should be 1 likewise go on.
What I tired is I can get count for two cells only using following formula =COUNTIFS(A1, "=A",A2,"=B")
But I don't how to go on increasing the rows.
Please help

回答1:

You may use SUMPRODUCT.
List all possible combinations (e.g., in column C below). Then in D1 use =SUMPRODUCT((($A$1:$A$8&$A$2:$A$9)=C1)*1). Copy downwards.
Or list all possible combinations using two columns (e.g., in columns F,G below). Then in H1 use =SUMPRODUCT(($A$1:$A$8=F1)*($A$2:$A$9=G1)). Copy downwards.

Or you may use COUNTIFS as you meant to do.
Using two columns, in I1 use =COUNTIFS($A$1:$A$8,F1,$A$2:$A$9,G1). Copy downwards.

回答2:

Please look at the picture as I done the above in excel, may someone have more accurate answer.