Thank you so much.
It works the way i want.
Could you please explain a little bit more on the use of "--" that shown in
your function?
I would like to know more about the excel language.
Thanks



"T. Valko" wrote:

Try these:

For BC or any 2 letter combination:

=SUMPRODUCT(--(A1:A6="B"),--(A2:A7="C"))

For BCD or any 3 letter combination:

=SUMPRODUCT(--(A1:A5="B"),--(A2:A6="C"),--(A3:A7="D"))

For ABCD or any 4 letter combination:

=SUMPRODUCT(--(A1:A4="A"),--(A2:A5="B"),--(A3:A6="C"),--(A4:A7="D"))

Note the progressive offsets of the ranges based on the number of letters
being counted.


--
Biff
Microsoft Excel MVP


"Ting Li" wrote in message
...
Hi everyone
I have got a column of data look like belows:
A
B
C
D
B
C
A
I want to find out the frequecy of certain consecutive patterns and
generate
outputs like:
BC 2
BCD 1
BCA 1
ABCD 1

Which function(s) in Excel I can make us of ?