Count Unique Values across multiple columns within a specific Date Range

[KlayontKress]

I'm looking for a way to count unique values using criteria in 3 columns (I need to find the unique values comparing all three columns) as well as only counting values between a specific date range. I have a series of formulas to count the number of items in a single column that are between a date range and contain a specific text and I tried to adapt another formula, listed below, to include this date range counting criteria but it doesn't work. When I tried running an error checker, it appears to count the first instance and then stops counting.

The formula I found online to count unique items across criteria in multiple columns is:

=SUMPRODUCT((1/COUNTIFS(U9:U21,U9:U21,V9:V21,V9:V21,W9:W21,W9:W21)))

I have another formula that counts items across a date range that is also compare multiple criteria:

=COUNTIFS('Breakdown Data (Don''t Filter)'!$L$2:$L$300000,"*00000*",[COLOR=#ff0000]'Breakdown Data (Don''t Filter)'!$S$2:$S$300000,">="&$B$1,'Breakdown Data (Don''t Filter)'!$S$2:$S$300000,"<="&$B$2[/COLOR],'Breakdown Data (Don''t Filter)'!$F$2:$F$300000,$A8)

I am attempting to use the red text above to modify the formula listed above that to count unique values across 4 columns.



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Number[/TD]
[TD]Letter[/TD]
[TD]Code[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]1/10/18[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Bob
[/TD]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]ZZ1
[/TD]
[TD]1/10/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD]1/15/18[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Bob
[/TD]
[TD]1
[/TD]
[TD]B
[/TD]
[TD]ZZ1
[/TD]
[TD]1/10/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Bob
[/TD]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]ZZ1
[/TD]
[TD]1/10/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD]# of unique items[/TD]
[TD](should be 5 entries, between the date range and across all 4 columns. They are the red text items to the left)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bob[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]ZZ1[/TD]
[TD]1/11/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]ZZ1[/TD]
[TD]1/8/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]John
[/TD]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]ZZ1
[/TD]
[TD]1/13/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Susan[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]ZZ1[/TD]
[TD]1/8/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Susan[/TD]
[TD]1[/TD]
[TD]B[/TD]
[TD]ZZ1[/TD]
[TD]1/9/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Joe
[/TD]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]ZZ1
[/TD]
[TD]1/13/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Joe[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]ZZ1[/TD]
[TD]1/11/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Joe[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]ZZ1[/TD]
[TD]1/16/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula I've tried is:

=SUMPRODUCT((1/COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12,C2:C12,C2:C12,D2:D12,D2:D12,E2:E12,">="&$I$1,E2:E12,"<="&$I$2)

The formula returns a #Div/0! error


Does anyone know how to modify the formula to meet the criteria I need?


Thanks in advance for any help you can provide.

 

[Rahul1987]

In the above example , IF i select name - GIGI , Start Date & ENd date - 01/25/2010 then I should be able to get unique count as 1 and likewise for the other names, also if i select ALL in names dropdown it should give me all the unique counts

 

[Rahul1987]

Glad you got it squared away.

Eric pls help me out with my question

 

[Rahul1987]

Somebody pls help....

 

[Eric W]

First, you would do better to open a new thread. Not everybody is available all the time. Opening a new thread allows more people to see it and try to help.

Next, I don't see a lot of reason to add the employee name to the formula. If you're looking for a count of unique employee names, that automatically limits the result to 1 or 0. So here are two formulas that might do what you want:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1
2
3	Emp Names	Dates	State	Action	Accused ID		Count Unique
4	Gigi	25-Jan-10	UK	Termination	11111		3	# of unique names With Termination or Warning letter
5	Gigi	25-Jan-10	UK	Termination	11111		25-Jan-10	Start Date Drop Down
6	Gigi	25-Jan-10	UK	Termination	11111		03-Mar-16	End Date Drop Down
7	Don	25-Jan-10	IND	Termination	11111
8	Don	25-Jan-10	IND	Warning letter	1112		Country Dropdown	Names Drop down
9	Don	25-Jan-10	IND	Warning letter	1112		UK	GIGI
10	Don	25-Jan-10	IND	Warning letter	1112		IND	RAHUL
11	Don	25-Jan-10	IND	Warning letter	573483		AUS	ASHWIN
12	Rahul	03-Mar-16	HK	Warning letter	573483		SG	ATUL
13	Rahul	03-Mar-16	HK	Warning letter	573483		ALL	DON
14	Rahul	03-Mar-16	HK	Shraddha	GER			ALL
15	Rahul	03-Mar-16	HK	Shraddha	GER
16	Ashwin	03-Mar-16	SG	Shraddha	GER		1	Including Country/Name
17	Ashwin	03-Mar-16	SG	Shraddha	GER		IND	All
18	Atul	03-Mar-16	AUS	Shraddha	GER
19	Atul	03-Mar-16	AUS	Shraddha	GER
20	Atul	03-Mar-16	AUS	Shraddha	GER
Sheet32

Cell Formulas
Range	Formula
G4	G4	=SUM(SIGN(FREQUENCY(IF((B4:B20>=G5)*(B4:B20<=G6)*ISNUMBER(MATCH(D4:D20,{"Termination","Warning letter"},0)),MATCH(A4:A20,A4:A20,0)),ROW(A4:A20)-ROW(A2)+1)))
G16	G16	=SUM(SIGN(FREQUENCY(IF((B4:B20>=G5)*(B4:B20<=G6)*ISNUMBER(MATCH(D4:D20,{"Termination","Warning letter"},0))*((C4:C20=G17)+("all"=G17))*((A4:A20=H17)+("all"=H17)),MATCH(A4:A20,A4:A20,0)),ROW(A4:A20)-ROW(A2)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rahul1987]

Hi Eric,

Thanks a lot , however, I solved this yesterday night coz I had to submit my dashboard today early morning. appreciate your assistance with this matter.