# Count number of times a number appears

#### Edgie

##### Board Regular
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### VoG

##### Legend
Try

=COUNTIF(A2:A10,A1)

#### Andrew Poulsom

##### MrExcel MVP
In B2:

=COUNTIF(A\$2:A2,A2)

copied down.

#### Edgie

##### Board Regular
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

##### MrExcel MVP
Try:

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

#### Edgie

##### Board Regular
Amazing....

Thanks very much Andrew

Replies
3
Views
52
Replies
7
Views
115
Replies
8
Views
92
Replies
9
Views
184
Replies
1
Views
49

1,127,798
Messages
5,626,951
Members
416,211
Latest member
lanka123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back