Excel Count Question

[lenebene]

I have a spreadsheet with the following:

fa1234 6.2
fa1234 3.2
fa1234 2.5
fa1235 9.5
fa1236 1
fa1237 3
fa1237 6.3

I would like to count how many times an item appears in the first column... So it would look like this:

fa1234 6.2 1
fa1234 3.2 2
fa1234 2.5 3
fa1235 9.5 1
fa1236 1 1
fa1237 3 1
fa1237 6.3 2

What function can I use or code to do this?

Thanks!
Charlene

 

[KenWright]

Assuming your data in A2:B100, in C2 put the following and copy down:-

=SUMPRODUCT(--($A$2:$A$100=A2),--($B$2:$B$100=B2))

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Forget that, I read the question wrong. You need the following in C2 and then copy down:-

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

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[lenebene]

I pasted what you posted... you assumed everything right but I am only getting 1's???

Thanks!
Charlene

 

[lenebene]

Nevermind the second answer worked!!

Thank you!!
Charlene

 

[KenWright]

You're welcome.



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

lenebene,

Welcome to Tek-Tips. If you stick around, you'll find that there are tons of extremely knowledgeable people who are extremely generous with that knowledge as well as their time (such as KenWright).

Since Ken was able to provide you with the answer you were looking for, it is customary for you to click the little link under one of Ken's posts that says Thank KenWright for this valuable post!

That will place a little purple star by his name in this thread and will also place a little purple star by the thread title in the list of threads, indicating that this thread was successfully answered. That is beneficial to future visitors who are searching for answers similar to the one you had.

Again, Welcome to the board. Please contribute and enjoy!

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.