Countif with the changing of column values

[jeffreybrown]

Seems like I've seen this before, but failing to find it.

In E2 =COUNTIF($C$2:C2,C2) to count the uniques within the category of North in column A.

With a change of category in A, East, the formula is manually updated to =COUNTIF($C$7:C7,C7) and so on.

How can this be modified to be dynamic based off of the change in category of column A?

Excel Workbook
	A	B	C	E
1	Col1	Col2	Col3	Col4
2	North	Jan	B001B	1
3	North	Jan	BQM167A	1
4	North	Jan	C005M	1
5	North	Feb	B001B	2
6	North	Feb	BQM167A	2
7	East	Mar	C020C	1
8	East	Apr	C020E	1
9	East	May	CVLSP	1
10	East	May	E003B	1
11	East	May	CVLSP	2
12	South	June	E003B	1
13	South	June	C020C	1
14	South	June	C020E	1
15	South	July	CVLSP	1
16	South	July	E003B	2
17	West	August	B001B	1
18	West	August	BQM167A	1
19	West	August	C005M	1
20	West	August	B001B	2
21	West	September	BQM167A	2
Sheet2

Excel 2007

Cell Formulas
Range	Formula
E2	=COUNTIF($C$2:C2,C2)
E7	=COUNTIF($C$7:C7,C7)
E12	=COUNTIF($C$12:C12,C12)
E17	=COUNTIF($C$17:C17,C17)

 

[gsistek]

Try:

=COUNTIFS($A$2:A2,A2,$C$2:C2,C2)

and copy down

 

[jeffreybrown]

Thanks, works great

 

[JoeMo]

Or, if you are pre-Excel 2007, in A2 and copy down:

=IF(A1="Col1",1,IF(A2=A1,COUNTIF(C$1:C2,C2),1))

 

[jeffreybrown]

Thanks Joe...2007 will work for me.