Formula for column for (sort of) running count

[devries1919]

Hi,

Can anybody help me with the formula for the following:

I need sort of a running count:
Column A is the combination of the 1) the competition number and 2) the player number.
For each new competition number, I would like to have a new running count that start again at 1.
And for each new player number within that same competition number, the running count should be increased with 1.

Please see an example in the image attached.

With the formula for the results in either column D or column E I would be very happy.

Thank you for your feedback!


PS1 The result of this column, I am going to use in a sumproduct formula.
PS2 I tried to install the plugin for mini-sheets but this plugin option was greyed out (see attachment 2), sorry for that!

 

[Cubist]

Since you're using older version of Excel, this is best I can come up with using a helper column B (Competition). Column C (player) isn't being used so you can remove.

Book1
	A	B	C	D
1
2	Combined	Competition	Player	Count
3	Competition 1 - Player 1	Competition 1	Player 1	1
4	Competition 1 - Player 2	Competition 1	Player 2	2
5	Competition 1 - Player 3	Competition 1	Player 3	3
6	Competition 1 - Player 4	Competition 1	Player 4	4
7	Competition 1 - Player 5	Competition 1	Player 5	5
8	Competition 1 - Player 4	Competition 1	Player 4
9	Competition 1 - Player 5	Competition 1	Player 5
10	Competition 1 - Player 4	Competition 1	Player 4
11	Competition 1 - Player 5	Competition 1	Player 5
12	Competition 2 - Player 1	Competition 2	Player 1	1
13	Competition 2 - Player 1	Competition 2	Player 1
14	Competition 2 - Player 1	Competition 2	Player 1
15	Competition 2 - Player 4	Competition 2	Player 4	2
16	Competition 2 - Player 5	Competition 2	Player 5	3
17	Competition 2 - Player 3	Competition 2	Player 3	4
18	Competition 2 - Player 5	Competition 2	Player 5
19	Competition 2 - Player 4	Competition 2	Player 4
20	Competition 2 - Player 5	Competition 2	Player 5
21	Competition 1 - Player 4	Competition 1	Player 4
22	Competition 1 - Player 5	Competition 1	Player 5
Sheet1

Cell Formulas
Range	Formula
B3:B22	B3	=TRIM(MID(A3,1,FIND("-",A3)-1))
C3:C22	C3	=TRIM(MID(A3,FIND("-",A3)+1,LEN(A3)))
D3:D22	D3	=IF(COUNTIFS($A$3:B3,A3)<>1,"",IF(COUNTIFS($B$3:B3,B3)=1,1,LOOKUP(2,1/ISNUMBER($D$2:D2),$D$2:D2)+1))

 

[devries1919]

Worked like a charm. Thank you very much for this solution!

 

[Cubist]

Worked like a charm. Thank you very much for this solution!

You’re welcome.

 

[Peter_SSs]

If you wanted to do it without a helper column you could try these.

24 11 10.xlsm
	A	B	C
1
2	Combined
3	Competition 1 - Player 1	1	1
4	Competition 1 - Player 2	2	2
5	Competition 1 - Player 3	3	3
6	Competition 1 - Player 4	4	4
7	Competition 1 - Player 5	5	5
8	Competition 1 - Player 4	4
9	Competition 1 - Player 5	5
10	Competition 1 - Player 4	4
11	Competition 1 - Player 5	5
12	Competition 2 - Player 1	1	1
13	Competition 2 - Player 1	1
14	Competition 2 - Player 1	1
15	Competition 2 - Player 4	2	2
16	Competition 2 - Player 5	3	3
17	Competition 2 - Player 3	4	4
18	Competition 2 - Player 5	3
19	Competition 2 - Player 4	2
20	Competition 2 - Player 5	3
21	Competition 1 - Player 4	4
22	Competition 1 - Player 5	5
Running Count

Cell Formulas
Range	Formula
B3:B22	B3	=IFERROR(IFNA(VLOOKUP(A3,A$2:B2,2,0),MAXIFS(B$2:B2,A$2:A2,LEFT(A3,FIND("-",A3))&"*")+1),1)
C3:C22	C3	=IF(COUNTIF(A$2:A2,A3),"",MAXIFS(C$2:C2,A$2:A2,LEFT(A3,FIND("-",A3))&"*")+1)

PS2 I tried to install the plugin for mini-sheets but this plugin option was greyed out

Go back to the installation instructions page and look at the 'Known XL2BB issues' section near the top, particularly the one labelled 'XL2BB Icons greyed out'

 

[devries1919]

If you wanted to do it without a helper column you could try these.

Thank you very much for these solutions, they worked perfect!

 

[devries1919]

Since you're using older version of Excel, this is best I can come up with using a helper column B (Competition). Column C (player) isn't being used so you can remove.

Just found a small error in the solution provided. In case I change for example row number 6 from "Competition 1 - Player 4" to "Competition 2 - Player 4", I noticed that the outcome switches to '1' (which is correct) but afterward that the next row switches to '2' which is incorrect. The solution provided from "Peter_SSs" did not have this issue and worked (and therefore I marked that one as the solution).

Many thanks to you both for the answers provided!

 

[Peter_SSs]

Thank you very much for these solutions, they worked perfect!

You're welcome. Thanks for the follow-up.