Excel - Nested Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
Click to expand...

and the table below is in Sheet2, in A1:D4
(w/o the results in B2:D4 ..)
A B C
aaa 5 3 2
bbb 2 4 4
ccc 5 3 2
Click to expand...

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 B2:D4
 
Back
Top