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.

 

[xxrichbxx]

@Fluff you my friend are a gent. Thanks for your help, works perfectly !!

 

[xxrichbxx]

I jumped the gun ! If a cell returns #CALC! that would suggest it can't find anything which meets the criteria. How would I set it to leave the cell blank?

 

[Fluff]

Like
=IFERROR(ROWS(UNIQUE(FILTER('Master Tracker'!$C$2:$C$486,'Master Tracker'!$A$2:$A$486=$A4))),"")

 

[xxrichbxx]

I have another question about this. I want to add another criteria, this being a project name. If I had a project name in column E is there a way I could use a specific reference to pull out the unique cell id in a given week, with a specific project?

Count/sumifs is straightforward enough but the array you have described above has messed with my head !

 

[Fluff]

Can you please post sample data & expected results.

 

[xxrichbxx]

This is the raw data

Book3
	A	B	C	D	E
1	Week No	PERMIT REFERENCE	CELL/SITE ID	START DATE	Project
2	28	REQ000000757087	ALD027	07-Jul-20	Leeds
3	28	REQ000000767121	ANT257	10-Jul-20	Barnsley
4	28	REQ000000755222	BAH017	08-Jul-20	Brentord
5	28	REQ000000717947	BAN028	08-Jul-20	Leeds
6	28	REQ000000717946	BAN028	06-Jul-20	Leeds
7	28	REQ000000778562	BAR027	10-Jul-20	Barnsley
8	28	REQ000000753657	BEX093	07-Jul-20	WBA
9	29	REQ000000798150	BAN028	13-Jul-20	WBA
10	29	REQ000000798156	BAN028	14-Jul-20	WBA
11	29	REQ000000798159	BAN028	15-Jul-20	WBA
Master Tracker

Cell Formulas
Range	Formula
A2:A11	A2	=WEEKNUM(D2,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	duplicates	text	NO

Main Summary as before

Book3
	K	L	M
5	Overall Summary
6	Week	Number of Sites	Number of Permits
7	28	6	7
8	29	2	3
Master Tracker

Then the new summary which breaks down the by project

Book3
	O	P	Q
5	Project Summary		Barnsley
6	Week	Number of Sites	Number of Permits
7	28	2	2
8	29	1	1
9
10	Project Summary		WBA
11	Week	Number of Sites	Number of Permits
12	28	1	1
13	29	1	2
Master Tracker

Hope it makes sense

 

[Fluff]

Ok, how about
=IFERROR(ROWS(UNIQUE(FILTER('Master Tracker'!$C$2:$C$486,('Master Tracker'!$A$2:$A$486=$O7)*('Master Tracker'!$E$2:$E$486='Master Tracker'!Q$5)))),"")

 

[xxrichbxx]

that's amazing. How do you know all this stuff?

 

[Fluff]

Just by helping people on here & playing around.

 

[xxrichbxx]

so pretty much self taught. Maybe one day I will get there