counting single entries where duplicates appear within a specific date range

[xxrichbxx]

I am trying to create a count which will essentially only count a single entry from a range where there could be multiples.

I was looking at a combination of countifs and frequency, but was struggling to get my head around it.

I think that an example of my source data and what I expect to see would give a better understanding of the problem

Week Number Cell ID Date
29 12345 13/07/2020
29 98765 14/07/2020
29 12345 17/07/2020
30 12345 20/07/2020
30 12345 21/07/2020
30 98765 21/07/2020
30 11111 22/07/2020

Summary

Week 29 2
Week 30 3

Hopefully that makes sense.

 

[Fluff]

so pretty much self taught.

Yup, that's right.

 

[xxrichbxx]

I have a follow on from the question raised. Previously I was look at week numbers, so example week 29 is the date range of 20/07/2020 to 26/07/2020.

The previous function was =IFERROR(ROWS(UNIQUE(FILTER('Master Tracker'!$C$2:$C$486,'Master Tracker'!$A$2:$A$486=$A3))),"") with a3 being the week number. What I need to do is rather than showing A3 as week 29 I am wanting to show it as a range.

A simple countifs would work, but then I have the added complexity of having an ID appearing multiple times.

 

[Fluff]

If you mean you want to replace the week numbers with something like
20/07/2020 - 26/07/2020
Then my advice would be don't, a cell should only hold one piece of data, not ranges.

 

[xxrichbxx]

agree completely with you, a range within a cell...massive no no.

I am trying to create something like the below

TA PACS Faults Week ending 22.07.2020 new.xlsx
	A	B	C	D	E	F	G	H	I
1	Cell ID	Date Created		Month	Date Range		Sites	Instances	Avg. instances
2	13759	09-Jul-20		June	01-Jun	30-Jun	4	7	1.8
3	13759	09-Jul-20		July	01-Jul	31-Jul	3	6	2.0
4	11746	10-Jun-20
5	11746	13-Jun-20
6	13711	02-Jun-20
7	13711	02-Jun-20
8	11746	10-Jun-20
9	11746	10-Jun-20
10	11746	11-Jul-20
11	11746	11-Jul-20
12	13776	20-Jun-20
13	13776	20-Jul-20
14	13776	21-Jul-20
15
Raw data

As you will see columns E&F are the date range. I am trying to glean the data from A&B.

Hopefully this makes sense.

 

[Fluff]

Ok in G2
=ROWS(UNIQUE(FILTER($A$2:$A$14,($B$2:$B$14>=$E2)*($B$2:$B$14<=$F2))))
in H2
=ROWS(FILTER($A$2:$A$14,($B$2:$B$14>=$E2)*($B$2:$B$14<=$F2)))

 

[xxrichbxx]

I can't seem to get that to work with my data. Could it be because I have dates prior to the range?

 

[Fluff]

That shouldn't make a difference, in what way isn't it working?

 

[xxrichbxx]

it doesn't appear to be returning the correct results.

I have used it on a much larger sheet replicating what both you and I have added.

The number of sites added is showing 319, but the correct result is 62.

I have increase the data range to include whole column or a specific field set, but it returns the same result.

 

[Fluff]

Are you sure that all your dates are real dates & not text?

 

[xxrichbxx]

Are you sure that all your dates are real dates & not text?

yes. I am pulling through the correct data when I use countifs within the date ranges within the instances function