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

ID	Name	Date
A112	John	01/01/2018
A112	John	01/02/2018
A113	John	01/03/2018
A114	Dave	01/01/2018
A115	Jenny	01/01/2018

<tbody>
</tbody>

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]

A​ B​ C​ D​ E​ F​ 1​ ID Name Date A112 John 2​ A112 John 1/1/2018​ 2​ 3​ A112 John 2/1/2018​ 4​ A113 John 3/1/2018​ 5​ A114 Dave 1/1/2018​ 6​ A115 Jenny 1/1/2018​ <tbody> </tbody>

<tbody>
</tbody>

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]

A​ B​ C​ D​ E​ F​ 1​ ID Name Date A112 John 2​ A112 John 1/1/2018​ 2​ 3​ A112 John 2/1/2018​ 4​ A113 John 3/1/2018​ 5​ A114 Dave 1/1/2018​ 6​ A115 Jenny 1/1/2018​ <tbody> </tbody>

<tbody>
</tbody>

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))

	A	B	C	D	E	F	G	H
1	ID	Name	Date		Name	Start	End	Result
2	A112	John	01/01/18		John	01/01/18	30/04/18	2
3	A112	John	02/01/18
4	A113	John	03/01/18
5	A114	Dave	01/01/18
6	A115	Jenny	01/01/18

<colgroup><col width="70" span="9" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[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))

	A	B	C	D	E	F	G	H
1	ID	Name	Date		Name	Start	End	Result
2	A112	John	01/01/18		John	01/01/18	30/04/18	2
3	A112	John	02/01/18
4	A113	John	03/01/18
5	A114	Dave	01/01/18
6	A115	Jenny	01/01/18

<tbody>
</tbody>

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!