Counting duplicate name once within a date range

A

alanhather

New Member
Joined
Feb 2, 2018
Messages
24
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
[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 ?
 
Last edited:
Upvote 0
MARZIOTULLIO said:
[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 ?
Click to expand...

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
 
Upvote 0
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]
 
Upvote 0
admiral100 said:
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]
Click to expand...
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.
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
229
Omran Y
O
A
  • Solved
Extract data based on matching headers with sheets names across sheets
Replies
4
Views
615
Ali M
A
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
1K
Insurance Dave
I
H
  • Solved
populate dynamic list in dropdown without duplicates items based on sheet name
Replies
3
Views
326
kevin9999
K
O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
888
OilEconomist
O

Forum statistics

Threads
1,225,749
Messages
6,186,802
Members
453,373
Latest member
Ereha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top