Dear JP,
I am comfortable with finding the number of AA. But what I need the formula
to provide is the "AA" itself. Because in the countif function i cannot put
AA as my list is very huge. So if the formula can pull out AA from the list
then in the countif criteria i will just have to link it to that cell.

"JP" wrote:

Sorry I meant COUNTIF, not COUNTA.

--JP

On Oct 16, 2:42 pm, JP wrote:
You would use the MATCH formula to return the row where the date is
found, then the COUNTA formula to count the number of instances of
each letter pair.

For example, if cell A1 on Sheet 2 contains the dropdown, and you
select "17.10.08", then this formula would return the row where it was
found on Sheet 1:

=MATCH(A1,Sheet1!A1:A3,0)

and this formula builds on that to count how many "AA" are in that
row:

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),"AA")

The formulas above are based on the sample data you provided in your
original post.

--JP