incremental counting based on criteria

S

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.
 
S

style0

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

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
 
S

style0

Got it, thanks again!

Domenic said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top