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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Occurrence (Future state)[/TD]
[/TR]
[TR]
[TD="align: center"]03.01.2018[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]03.01.2018[/TD]
[TD="align: center"]Steve[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]03.01.2018[/TD]
[TD="align: center"]Paul[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]05.08.2018[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]05.08.2018[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]05.08.2018[/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]06.15.2018[/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]06.15.2018[/TD]
[TD="align: center"]Paul[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]06.15.2018[/TD]
[TD="align: center"]Paul[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]07.22.2018[/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]07.22.2018[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]07.22.2018[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

Try this:

Add another column (helper)

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


[TABLE="width: 364"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]Helper[/TD]
[TD]Occurrence[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/03/18[/TD]
[TD]Joe[/TD]
[TD]Joe9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/03/18[/TD]
[TD]Steve[/TD]
[TD]Steve9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/03/18[/TD]
[TD]Paul[/TD]
[TD]Paul9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]08/05/18[/TD]
[TD]Joe[/TD]
[TD]Joe19[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]08/05/18[/TD]
[TD]Joe[/TD]
[TD]Joe19[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]08/05/18[/TD]
[TD]Chris[/TD]
[TD]Chris19[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]15/06/18[/TD]
[TD]Chris[/TD]
[TD]Chris24[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]15/06/18[/TD]
[TD]Paul[/TD]
[TD]Paul24[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]15/06/18[/TD]
[TD]Paul[/TD]
[TD]Paul24[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]22/07/18[/TD]
[TD]Chris[/TD]
[TD]Chris30[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]22/07/18[/TD]
[TD]Joe[/TD]
[TD]Joe30[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]22/07/18[/TD]
[TD]Sam[/TD]
[TD]Sam30[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]