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

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]col 1[/TD]
[TD]Col2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]



The results i would like are:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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