Running Count of Value by Week

[vintage18K]

Hello- Thanks in advance for any assistance.

Looking to have a column in excel which calculates the number of times a name appears weekly in a data set over time and assigns an integer to the name.

The "Occurrence" column would auto populate the above target value.

I was using '= COUNTIF($A$1:$1,1)' and it was working fine but duplicates have started to occur weekly in the data which has thrown off the weekly count. In the below example Joe populated in March once and May two times. In the below example Joe would get a 1 assigned to him for the first month he occurred in March and a 2 assigned to each occurrence in May [My COUNTIF formula would have counted Joe's 3rd time in the column as a 3 when technically it should be listed as a 2 since its the 2nd week he populated]

Conditions: I cannot delete any of the data set.

Date	Value	Occurrence (Future state)
03.01.2018	Joe	1
03.01.2018	Steve	1
03.01.2018	Paul	1
05.08.2018	Joe	2
05.08.2018	Joe	2
05.08.2018	Chris	1
06.15.2018	Chris	2
06.15.2018	Paul	2
06.15.2018	Paul	2
07.22.2018	Chris	3
07.22.2018	Joe	3
07.22.2018	Sam	1

<tbody>
</tbody>

 

[admiral100]

Hi,

Try this:

Add another column (helper)

C2 =B2&WEEKNUM(A2)
D2 =COUNTIF($C$2:$C$13,C2)

	A	B	C	D
1	Date	Value	Helper	Occurrence
2	01/03/18	Joe	Joe9	1
3	01/03/18	Steve	Steve9	1
4	01/03/18	Paul	Paul9	1
5	08/05/18	Joe	Joe19	2
6	08/05/18	Joe	Joe19	2
7	08/05/18	Chris	Chris19	1
8	15/06/18	Chris	Chris24	1
9	15/06/18	Paul	Paul24	2
10	15/06/18	Paul	Paul24	2
11	22/07/18	Chris	Chris30	1
12	22/07/18	Joe	Joe30	1
13	22/07/18	Sam	Sam30	1

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>