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.

	A	B	C	D	E	F	G	H	I
1	Name	Number	Letter	Code	Date			Start Date	1/10/18
2	Bob	1	A	ZZ1	1/10/18			End Date	1/15/18
3	Bob	1	B	ZZ1	1/10/18
4	Bob	2	A	ZZ1	1/10/18			# of unique items	(should be 5 entries, between the date range and across all 4 columns. They are the red text items to the left)
5	Bob	2	A	ZZ1	1/11/18
6	John	1	A	ZZ1	1/8/18
7	John	2	A	ZZ1	1/13/18
8	Susan	1	A	ZZ1	1/8/18
9	Susan	1	B	ZZ1	1/9/18
10	Joe	1	A	ZZ1	1/13/18
11	Joe	1	A	ZZ1	1/11/18
12	Joe	2	A	ZZ1	1/16/18
13
14
15
16
17
18
19

<tbody>
</tbody>

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.