Excel macro consolidating and counting

Question

I have a column of information. For example, aa, aa, aa, bb, bb, cc, cc, cc, cc, cc, aa, aa. Beside this column is another column specifying the class of the previous column. For example, a, a, a, b, b, c, c, c, c, c, d, d. How do I count the columns such that aa of class a gives 3, bb of class b gives 2, cc of class c gives 5 and aa of class d gives 2? aa of class a is different from aa from class d, so they need to be counted separately.

Answer

Please refer the image.

Lets say column A1 to A12 contains aa, aa, aa, bb, bb, cc, cc, cc, cc, cc, aa, aa.

 Column B1 to B12 contains a, a, a, b, b, c, c, c, c, c, d, d.

Create another column (say C1 to C12) (can be hidden) with concatenation of A & B such that C1= A1&B1 , C2 = A2&B2, ..., C12 = A12&B12

And then

Create your summary as follows:

E1: aa, E2: bb, E3: cc, E4: aa F1: a, F2: b, F3: c, F4: d

G1: =COUNTIF($C:$C,E1&F1) G2: =COUNTIF($C:$C,E2&F2) G3: =COUNTIF($C:$C,E3&F3) G4: =COUNTIF($C:$C,E4&F4)

If you would needfile, please send me your email id.

Ariful