Counting values in a table

[Legacy 93538]

I have a tbale similar to below and i need get results which show how many non blank values in col2 for each unqiue value in col 1

col 1	Col2
1
1
2	a
2	b
2	c
3
3
4	a

<tbody>
</tbody>

The results i would like are:

1	0
2	3
3	0
4	1

<tbody>
</tbody>

Does anyone have any idea of how to do this? I have tried using a countifs formula (COUNTIFS(Sheet1!$b:$b,"<>"&"",Sheet1!$a:$a,A2)) however i didn't work.

Any help would be appricated.

 

[James006]

Hello,

Make your life simple ...

Insert a Pivot Table ... you will get your results in less than 5 seconds ...

Hope this will help

 

[Fluff]

How about
=COUNTIFS(Sheet1!$B:$B,"?*",Sheet1!$A:$A,A2)

 

[Legacy 93538]

The countif worked perfectly thank you

 

[Fluff]

You're welcome & thanks for the feedback