Counting duplicate name once within a date range

[alanhather]

Hi All,

Currently doing a project and trying to collate data from a raw data sheet. I’m trying to count say a Alphanumeric ID in a range of dates. I’ve used SUMPRODUCT but still can’t quite get it. It looks something like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A112[/TD]
[TD]John[/TD]
[TD]01/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A112[/TD]
[TD]John[/TD]
[TD]01/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A113[/TD]
[TD]John[/TD]
[TD]01/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A114[/TD]
[TD]Dave[/TD]
[TD]01/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A115[/TD]
[TD]Jenny[/TD]
[TD]01/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So if I wanted to count between 01/01/2018 -31/04/2018 for John it would be is 2 as the ID is the same.

hope I’ve explained okay and thanks for the help in advance.

 

[MARZIOTULLIO]

[TABLE="width: 403"]
<tbody>[TR]
[TD][TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]A112[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A112[/TD]
[TD]John[/TD]
[TD]

1/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]A112[/TD]
[TD]John[/TD]
[TD]

2/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]A113[/TD]
[TD]John[/TD]
[TD]

3/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]A114[/TD]
[TD]Dave[/TD]
[TD]

1/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]A115[/TD]
[TD]Jenny[/TD]
[TD]

1/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


f2=COUNTIFS($A$2:$A$6,E1,B2:B6,F1,C2:C6,">="&C2,C2:C6,"<=4/30/2018")

april 30 days 31/4/2018 ?

 

[alanhather]

[TABLE="width: 403"]
<tbody>[TR]
[TD][TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]A112[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A112[/TD]
[TD]John[/TD]
[TD]

1/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]A112[/TD]
[TD]John[/TD]
[TD]

2/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]A113[/TD]
[TD]John[/TD]
[TD]

3/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]A114[/TD]
[TD]Dave[/TD]
[TD]

1/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]A115[/TD]
[TD]Jenny[/TD]
[TD]

1/1/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


f2=COUNTIFS($A$2:$A$6,E1,B2:B6,F1,C2:C6,">="&C2,C2:C6,"<=4/30/2018")

april 30 days 31/4/2018 ?

Thanks for the quick reply - I was trying to get it as I wouldn’t know what The I’d is as I would have 3000 records all unique. Is it possible to skip any duplications?

Thanks

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

H2 =SUM(--(FREQUENCY(IF(B2:B6=$E$2,IF(C2:C6>=$F$2,IF(C2:C6<=$G$2,MATCH(A2:A6,A2:A6,0)))),ROW(A2:A6)-ROW(A2)+1)>0))


[TABLE="width: 630"]
<colgroup><col width="70" span="9" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"][/TD]
[TD="class: xl65, width: 70"]A[/TD]
[TD="class: xl65, width: 70"]B[/TD]
[TD="class: xl65, width: 70"]C[/TD]
[TD="class: xl65, width: 70"]D[/TD]
[TD="class: xl65, width: 70"]E[/TD]
[TD="class: xl65, width: 70"]F[/TD]
[TD="class: xl65, width: 70"]G[/TD]
[TD="class: xl65, width: 70"]H[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Name[/TD]
[TD="class: xl65"]Date[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Name[/TD]
[TD="class: xl65"]Start[/TD]
[TD="class: xl65"]End[/TD]
[TD="class: xl65"]Result[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]A112[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl66"]01/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl67, align: right"]01/01/18[/TD]
[TD="class: xl67, align: right"]30/04/18[/TD]
[TD="class: xl65"] 2[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]A112[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl66"]02/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]A113[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl66"]03/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]A114[/TD]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl66"]01/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]A115[/TD]
[TD="class: xl65"]Jenny[/TD]
[TD="class: xl66"]01/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[alanhather]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

H2 =SUM(--(FREQUENCY(IF(B2:B6=$E$2,IF(C2:C6>=$F$2,IF(C2:C6<=$G$2,MATCH(A2:A6,A2:A6,0)))),ROW(A2:A6)-ROW(A2)+1)>0))


[TABLE="width: 630"]
<tbody>[TR]
[TD="class: xl65, width: 70"][/TD]
[TD="class: xl65, width: 70"]A[/TD]
[TD="class: xl65, width: 70"]B[/TD]
[TD="class: xl65, width: 70"]C[/TD]
[TD="class: xl65, width: 70"]D[/TD]
[TD="class: xl65, width: 70"]E[/TD]
[TD="class: xl65, width: 70"]F[/TD]
[TD="class: xl65, width: 70"]G[/TD]
[TD="class: xl65, width: 70"]H[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Name[/TD]
[TD="class: xl65"]Date[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Name[/TD]
[TD="class: xl65"]Start[/TD]
[TD="class: xl65"]End[/TD]
[TD="class: xl65"]Result[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]A112[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl66"]01/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl67, align: right"]01/01/18[/TD]
[TD="class: xl67, align: right"]30/04/18[/TD]
[TD="class: xl65"] 2[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]A112[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl66"]02/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]A113[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl66"]03/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]A114[/TD]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl66"]01/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]A115[/TD]
[TD="class: xl65"]Jenny[/TD]
[TD="class: xl66"]01/01/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

Hi thanks for the help. This worked great on the small chart I had but with the raw data sheet of 3000 records it just returns a value of 0. Not sure why this is.

 

[alanhather]

I think it is possible that there are some blank cells could this do that?

 

[admiral100]

Try this

=SUM(--(FREQUENCY(IF(B2:B6=$E$2,IF(A2:A6<>"",IF(C2:C6>=$F$2,IF(C2:C6<=$G$2,MATCH(A2:A6,A2:A6,0))))),ROW(A2:A6)-ROW(A2)+1)>0))

 

[alanhather]

This still didnt work - I've also tried to use this which again works on the small chart but when i try to use it on the raw data set returns 0

=SUMPRODUCT(--(C2:C6>=F2),--(C2:C6<=G2),--(A153:A164=B2),--1/COUNTIF(A2:A6,A2:A6))

This is the exact why im putting it in when inputting the formula

=SUM(--(FREQUENCY(IF('Raw Data Prev Year'!D:D=A168,IF('Raw Data Prev Year'!S:S>=Control!C26,IF('Raw Data Prev Year'!S:S<=Control!C27,MATCH('Raw Data Prev Year'!B:B,'Raw Data Prev Year'!B:B,0)))),ROW('Raw Data Prev Year'!B:B)-ROW('Raw Data Prev Year'!B2)+1)>0))

Thanks again

 

[alanhather]

This worked in the end i missed something off whist typing it in. Thanks for your help!