incremental counting based on criteria

[style0]

I am out of ideas. I need to count how many times a category is repeated.
Here's what I have:
name category
1 a aa
2 b aa
3 c aa
4 d bb
5 e aa
6 f aa
7 g bb
8 h cc
9 i cc
10 j aa

Here's what I need:
name category count
1 a aa 1
2 b aa 2
3 c aa 3
4 d bb 1
5 e aa 4
6 f aa 5
7 g bb 2
8 h cc 1
9 i cc 2
10 j aa 6

Same thing sorted (if it helps):
name category count
1 a aa 1
2 b aa 2
3 c aa 3
4 e aa 4
5 f aa 5
6 j aa 6
7 d bb 1
8 g bb 2
9 h cc 1
10 i cc 2

Thank you in advance.

 

[Domenic]

Try...

C1, copied down:

=COUNTIF($B$1:B1,B1)

Hope this helps!

http://www.xl-central.com

 

[style0]

Worked perfectly, thank you so much!
Now maybe you can help me understand what "$B$1:B1,B1" means.

 

[Domenic]

You're very welcome!

Basically, when the formula is first entered in C1, the range is $B$1:B1
and the criteria is B1. When the formula is copied and pasted or
dragged down to C2, the range changes to $B$1:B2 and the criteria
changes to B2.

As you can see, the starting reference for the range does not change and
remains at $B$1, whereas the ending reference for the range changes from
B1 to B2, and the criteria changes from B1 to B2.

That's because the starting reference is an absolute reference
(specified by the $ signs) and so does not change when copied, whereas
the reference for the ending reference and criteria are relative
references (relative to the cell housing the formula).

For more information, have a look at Excel's help files under 'absolute
and relative references'.

Hope this helps!

http://www.xl-central.com

 

[style0]

Got it, thanks again!

You're very welcome!

Basically, when the formula is first entered in C1, the range is $B$1:B1
and the criteria is B1. When the formula is copied and pasted or
dragged down to C2, the range changes to $B$1:B2 and the criteria
changes to B2.

As you can see, the starting reference for the range does not change and
remains at $B$1, whereas the ending reference for the range changes from
B1 to B2, and the criteria changes from B1 to B2.

That's because the starting reference is an absolute reference
(specified by the $ signs) and so does not change when copied, whereas
the reference for the ending reference and criteria are relative
references (relative to the cell housing the formula).

For more information, have a look at Excel's help files under 'absolute
and relative references'.

Hope this helps!

http://www.xl-central.com