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.

 

[jasonb75]

Please update your user profile to show which version of excel you are using (click your user name at the top right of this page, then go to 'Account details' scroll down and check the correct box, then scroll to the bottom and save.

It is preferable to only select the version that you use most often so that the correct answers can be provided. Some formulas for office 365 do not work in any of the older versions, but if you only use 365 then a formula for an older version will be more complicated than necessary.

 

[Fluff]

What version of Excel are you using?

Please udpate your account details to show this, as it affects what functions you can use.

 

[xxrichbxx]

Hi both, didn't realise I had to.....

I am on Excel 365

 

[Fluff]

Thanks for that
As you have 365 you may well have the dynamic array functions, like

+Fluff New.xlsm
	A	B	C	D	E	F
1
2	29	12345	13/07/2020		29	2
3	29	98765	14/07/2020		30	3
4	29	12345	17/07/2020
5	30	12345	20/07/2020
6	30	12345	21/07/2020
7	30	98765	21/07/2020
8	30	11111	22/07/2020
9
Main

Cell Formulas
Range	Formula
F2:F3	F2	=COUNT(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=E2)))

 

[jasonb75]

Hi both, didn't realise I had to.....

It's not mandatory, just makes things much easier for people helping you. Without the dynamic array functions that @Fluff has used, the formula would be far more complex and less efficient. The formula that @Fluff has provided will not work with older versions, but it is definitely preferable to the alternative if you are able to use it.

 

[xxrichbxx]

@Fluff @jason75 thanks both.

Tried this and it doesn't look to be working (without doubt user error).

The function I used was =COUNT(UNIQUE(FILTER('Master Tracker'!$C$2:$C$486,'Master Tracker'!$A$2:$A$486=$A32))) which has unfortunately returned zero where I would have expected to see 149.

column C on the master tracker is the Cell ID and column A is the week number. The week number is calculated using the function Weeknum, but I am sure that this doesn't cause a problem.

 

[Fluff]

In that case can you please post some sample data using the XL2BB add-in.

 

[xxrichbxx]

I think that I have done what you need.

This is the data I am using. Columns A & C are the columns I need.

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

with the below summary

Book2
	A	B	C
3	Week	Number of Sites	Number of Permits Requested
29	28	0
30	29	0
Dashboard

 

[Fluff]

The problem is that col C is text & not numbers (as shown in your op).
Try
=ROWS(UNIQUE(FILTER('Master Tracker'!$C$2:$C$486,'Master Tracker'!$A$2:$A$486=$A4)))