Count Row Skipping Duplicates in Date Range

[jessebh2003]

I have a dynamic spreadsheet that updates values based on the year selected in a drop down on worksheet Summary and my data is in Table13 on worksheet Data. I'm trying to figure out how to count the number of rows within the date but skip duplicates.

Summary.xlsx
	C		E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Year		2021
2	Months		1		2		3		4		5		6		7		8		9		10		11		12		YTD
3	Evaluations
4			Count		Count		Count		Count		Count		Count		Count		Count		Count		Count		Count		Count		Count
5	Total submissions																										0.00
Summary

Cell Formulas
Range	Formula
AC5	AC5	=SUM($E$5:$AB$5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:AD5	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E1	List	=Years

For instance, in the below table I would like to count rows 2-6 as 1 because the data in columns A-E are the same; count rows 7-14 as 1, etc. Where I'm running into issues is the counting but skipping duplicates. I have the date range part of the formula already. (The data is sorted to make viewing easier but the table has over 22,000 rows.)

,Table13[SubmissionDate],">="&DATE($E$1,$E$2,1),Table13[SubmissionDate],"<="&DATE($E$1,$E$2,31)

Summary.xlsx
	A	B	C	D	E
1	FormID	UserID	EventID	RecurrenceParentID	SubmissionDate
2	1637	32	8531	8390	1/29/2021 10:08
3	1637	32	8531	8390	1/29/2021 10:08
4	1637	32	8531	8390	1/29/2021 10:08
5	1637	32	8531	8390	1/29/2021 10:08
6	1637	32	8531	8390	1/29/2021 10:08
7	2213	32	10717	0	10/16/2021 11:04
8	2213	32	10717	0	10/16/2021 11:04
9	2213	32	10717	0	10/16/2021 11:04
10	2213	32	10717	0	10/16/2021 11:04
11	2213	32	10717	0	10/16/2021 11:04
12	2213	32	10717	0	10/16/2021 11:04
13	2213	32	10717	0	10/16/2021 11:04
14	2213	32	10717	0	10/16/2021 11:04
15	1993	32	10376	0	3/16/2021 15:44
16	1993	32	10376	0	3/16/2021 15:44
17	2262	32	10891	0	7/26/2021 10:20
18	2262	32	10891	0	7/26/2021 10:20
19	2262	32	10891	0	7/26/2021 10:20
20	2262	32	10891	0	7/26/2021 10:20
Data

Hope that all makes sense. Appreciate any help, ideas or suggestions on how I can count rows, skipping duplicates within a date range. Thank you!!

 

[SanjayGMusafir]

Try this -

=Count(Unique(Table13[SubmissionDate])

 

[jessebh2003]

Try this -

=Count(Unique(Table13[SubmissionDate])

Thanks. I added the missing close parenthesis at the end.

To add my date qualifiers to the formula and change to COUNTIFS, I get an error message, "You've entered too few argument for this formula." I need to be able to count the unique values given the date range where the year is in $E$1 and the month is in row 2 (Jan in E2, Feb in G2, Mar in I2, etc.).

=COUNTIFS(UNIQUE(Table13[SubmissionDate]),Table13[SubmissionDate],">="&DATE($E$1,$E$2,1),Table13[SubmissionDate],"<="&DATE($E$1,$E$2,31))

Also, in the unlikely event that two different users have a submission at the same time, would your solution count them as two unique values or one? Thanks.

 

[SanjayGMusafir]

I get an error message, "You've entered too few argument for this formula."

Try this - It took me a while to get to this. Someone might help you shorten this Formula.

=Let(
Filter1,
Filter(UNIQUE(Table13[SubmissionDate]),UNIQUE(Table13[SubmissionDate])>=DATE($E$1,$E$2,1)),
Filter2,
FILTER(FILTER1,FILTER1<=DATE($E$1,$E$2,31)),
COUNT(FILTER2))

two different users have a submission at the same time

If the date and time shall match it will take as one. Problem will be when date is same but time differs

 

[jessebh2003]

Try this - It took me a while to get to this. Someone might help you shorten this Formula.

=Let(
Filter1,
Filter(UNIQUE(Table13[SubmissionDate]),UNIQUE(Table13[SubmissionDate])>=DATE($E$1,$E$2,1)),
Filter2,
FILTER(FILTER1,FILTER1<=DATE($E$1,$E$2,31)),
COUNT(FILTER2))

If the date and time shall match it will take as one. Problem will be when date is same but time differs

Thanks, @SanjayGMusafir, that worked perfectly!! Very much appreciated!!

 

[SanjayGMusafir]

Glad to help you. Thanks for your patience.

 

[SanjayGMusafir]

=Let( Filter1, Filter(UNIQUE(Table13[SubmissionDate]),UNIQUE(Table13[SubmissionDate])>=DATE($E$1,$E$2,1)), Filter2, FILTER(FILTER1,FILTER1<=DATE($E$1,$E$2,31)), COUNT(FILTER2))

@jessebh2003 If you may note, this formula is dependent on many input cells. Also it needs to be changed with every month to tell how many days the month has.

There can be more dynamic way of doing it.

If we can put a date in cell E1 rather than putting a year, underneath formula can calculate rest based on beginning and end of that month. This way it shall be dependent on only one cell.

Check this -

=Let(UDate,UNIQUE(Table13[SubmissionDate]),
Filter1,Filter(UDate,UDate>=EOMONTH($E$1,-1)+1),
Filter2,FILTER(FILTER1,FILTER1<=EOMONTH($E$1,0)),
COUNT(FILTER2))