How to Create a Series that Counts Depending on Another Column

[Vog2654]

Hello,

I am trying to find a function that will count starting at 1 based on the column besides it. I need the count to start at 1 each time a new Document number is reported.

Example of what I am trying to achieve below:

Line	Document Number
1​	P-0001
2​	P-0001
3​	P-0001
4​	P-0001
5​	P-0001
6​	P-0001
1​	P-0002
2​	P-0002
1​	P-0003
2​	P-0003
3​	P-0003
4​	P-0003

Any ideas on how to solve this?

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Try

Book2
	A	B	C
1	Line	Document Number
2	1	P-0001
3	2	P-0001
4	3	P-0001
5	4	P-0001
6	5	P-0001
7	6	P-0001
8	1	P-0002
9	2	P-0002
10	1	P-0003
11	2	P-0003
12	3	P-0002
13	4	P-0002
14
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=TEXT(COUNTIF($A$2:A2,A2),"P-0000")

 

[Fluff]

My take on this

=COUNTIFS(B$2:B2,B2)

 

[Sufiyan97]

ohh, I interpreted it other way around

 

[Vog2654]

My take on this

=COUNTIFS(B$2:B2,B2)

Thank you!! This worked!

Can you explain the logic behind this formula? I do not fully understand how it works.

 

[Fluff]

It counts how many rows level with or above match the value in col B