Excel - Nested Formula

[Katie]

Can someone please help me. I have been trying to figure this one out all
day. It seems so simple, I guess I'm brain dead.

I'm trying to write a formula that will return results for the folowing
example:

aaa A A B B A
bbb C A B B C
ccc A A B B B
aaa C C A A B
bbb C A B B C
ccc A A C C A

I need a formula that will count the total number of times each of the colum
results (A,B and C) appear for each unique item in Column A (aaa,bbb,ccc).

The following is the results I'm looking for:
A B C
aaa 5 3 2
bbb 2 4 4
ccc 5 3 2

Thanks in advance for anyone that can help a dummy out!

 

[Max]

Assuming the data below is in Sheet1, in A1:F6

aaa A A B B A
bbb C A B B C
ccc A A B B B
aaa C C A A B
bbb C A B B C
ccc A A C C A

and the table below is in Sheet2, in A14
(w/o the results in B24 ..)

A B C
aaa 5 3 2
bbb 2 4 4
ccc 5 3 2

Put in B2:

=SUMPRODUCT((Sheet1!$A$1:$A$6=$A2)*(Sheet1!$B$1:$F$6=B$1))

Copy across to D2, then fill down to D4

This'll return the results in B24