Count value from B column based on A column content

Hi,

I will be greatful if someone could help me in solving this problem

Here is my query:
I have two columns with the following values in 1 Sheet:
ID Name
TC_A_1 Harry
TC_A_2 Harry
TC_A_3 Harry
TC_A_4 Gus
TC_B_1 Gus

I have another sheet with the following four columns:
ID_Count Tot_Count Harry Gus
TC_A_* 4 3 1
TC_B_* 1 0 1

Is it possibile to get the count of Harry and Gus for TC_A_* and TC_B_* in the second sheet as 3,1 and 0,1 respectively?

Hello harishg,

See attached. Note: there are only 2 different formulas. B2 formula can be copied down as far as required, C2 formula can be copied across and down as required

Hi,

That was exactly what I was struggling to get! I learnt one more function in excel sumproduct and how to use it. :-)

Thanks a lot.

- Harish