Count number of times a number appears

[Edgie]

Hi,

I have a list of numbers going down like so, with some blanks

(A2) 10
(A3) 11
(A4)
(A5) 10
(A6)
(A7) 10

In cell (A1) i would like to be able to put a number in to see how many times that number has appeared in the data below (from a2 onwards).

However in the next cell i would like to show how many times that number has occured.

e.g the number i want to count is 10,

so A1 i put 10
(A2) 10 (B2) 1
(A3) 11
(A4)
(A5) 10 (B5) 2
(A6)
(A7) 10 (B7) 3

I probably havent explained myself very well....so apologies.

Thanks

Matt

 

[VoG]

Try

=COUNTIF(A2:A10,A1)

 

[Andrew Poulsom]

In B2:

=COUNTIF(A$2:A2,A2)

copied down.

 

[Edgie]

Thanks very much for the responses....i'm not sure they are working.

The first formula counts how many times that number occurs. Not quite what i'm after.

I need to see what occurance of a selected number has occured in the list.

I have a range of numbers but i want to be able to choose what number i want to see how many times it has occured been in the range of numbers. So for this exmaple i input the number i want to see how many times it has occured in the range below. So in (A1) i type 10 and next to all the 10's in the range (a2) onwards, next to it will show how many times it is occured, so the first 10 will have 1, the next 10 will have 2 etc

(A) a user defined number - e.g 10
(A2) 10 (B2) 1
(A3) 0 (B3)
(A4) 7 (B4)
(A5) 10 (b5) 2

Thanks for you help guys

 

[Andrew Poulsom]

Try:

=IF(A2=A$1,COUNTIF(A$2:A2,A$1),"")

 

[Edgie]

Amazing....

Thanks very much Andrew