# Counting the number of times a number has appeared in a column

#### oli35

##### New Member
Hi,

I want to find and count the number of times a given number has appeared in the same column, based on its position in the column. So if it's the first time it appeared in the column, I want to return 1, if it's the second time it appeared, I want to return 2, etc. I'm thinking of using the formula count, but how do I tell it to "count this number up to this point", since the range is conditional to the position of the number?

An image is worth a thousand words :

Thanks a lot

Last edited:

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Assuming column A

=COUNTIF(A\$1:A1,A1)

Assuming your data begins in A1:

Code:
``=COUNTIF(\$A\$1:A1,A1)``

I always feel so silly when I post the EXACT thing that the person above me posted. Darned not refreshing.

I can't look at your sheet, but what error is it giving?

I always feel so silly when I post the EXACT thing that the person above me posted. Darned not refreshing.

You can't do that, I've got trademarks and copyrights on that silly feeling, you're stealing my unintellectual property

See if this works

=COUNTIF(B\$4:B4;B4)

Well it says the formula is not correct, and it highlights the A1,A1 part. I tried changing the letters to the correct ones, it gives the same error.

Great now it works, thanks misters. That's the one that worked in the end: =COUNTIF(A\$1:A1,A1)

Replies
2
Views
66
Replies
6
Views
238
Replies
9
Views
248
Replies
4
Views
265
Replies
19
Views
327

1,219,957
Messages
6,151,145
Members
451,011
Latest member
Pigdog89

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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