See these:

http://mcgimpsey.com/excel/formulae/doubleneg.html

This linked is temporarily "dead" but should be ok at the beginning of the
next month:

http://xldynamic.com/source/xld.SUMPRODUCT.html

It has an extensive discussion about SUMPRODUCT including the "--".

--
Biff
Microsoft Excel MVP


"Ting Li" wrote in message
...
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 ?