# 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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try

=COUNTIF(A2:A10,A1)

In B2:

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

copied down.

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

Try:

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

Amazing....

Thanks very much Andrew

Replies
1
Views
222
Replies
2
Views
94
Replies
5
Views
73
Replies
4
Views
118
Replies
2
Views
124

1,207,111
Messages
6,076,620
Members
446,216
Latest member
BEEALTAIR

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