excel - counting how many times the same number appears in a column wh 1

[jupops]

Good morning all, can any one help? This is most likely a simple question, I have two columns of data in a excel; spread sheet and I want to count how many times different numbers appeared in column 1 when the criteria is met in column 2. the results will then be displayed in another worksheet which used the lookup value from column B in worksheet 1. For example

worksheet 1 – data which I need to count

A B
678 6
678 6
678 5
73 5
890 `` 5

worksheet 2 – the result page

A B
5 3 (this has 3 numbers which are different assigned to 5 in worksheet 2 column)
6 1 (only has 1 number which is different even though there is two numbers assigned to 6)

Thank you for your help in advance

Jupops

 

[SkipVought]

hi,
[tt]
=COUNTIF(Sheet1!B:B,A1)
[/tt]
assuming that 5 is in A1

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jupops]

Hi

Thank you for the reply that worked, but you point me in the right direction as I need to count all the unique numbers associated with 5 for example

A B
12 5
12 5
17 5
12 5
16 5
12 6
12 6


Therefore the result for this would be 3 (because 12,17 and 16 were associated with 5)and 1 (because 12 is associated with 6, thank you again

 

[SkipVought]

Use the Advanced Filter to copy UNIQUE values to a new range. Do you stats referencing this table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jupops]

Thank you, After all my years of using Excel I have never used Advanced filter before, what a great littlre tool, it shows that we learn somthing every day.

Cheers

Jupops