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.

 

[Eric W]

How about:

=SUM(SIGN(FREQUENCY(IF(A2:A12<>"",IF(E2:E12>=I1,IF(E2:E12<=I2,MATCH(A2:A12&"|"&B2:B12&"|"&C2:C12&"|"&D2:D12,A2:A12&"|"&B2:B12&"|"&C2:C12&"|"&D2:D12,0)))),ROW(E2:E12)-ROW(E2)+1)))

confirmed with Control+Shift+Enter.

 

[KlayontKress]

that looks like it does the trick good sir.


thank you

 

[KlayontKress]

is there a way to modify this to see how many non duplicate items joe has?

 

[KlayontKress]

nvm. I figured out where to put the if statement to narrow it down to a single person after breaking apart the formula. TY again

 

[Eric W]

Glad you got it squared away.

 

[Rahul1987]

How about:

=SUM(SIGN(FREQUENCY(IF(A2:A12<>"",IF(E2:E12>=I1,IF(E2:E12<=I2,MATCH(A2:A12&"|"&B2:B12&"|"&C2:C12&"|"&D2:D12,A2:A12&"|"&B2:B12&"|"&C2:C12&"|"&D2:D12,0)))),ROW(E2:E12)-ROW(E2)+1)))

confirmed with Control+Shift+Enter.

Hi Eric,

I need your help here
I have below columns

1. name of employee
2. Date column
3. Case reference
4. Accused ID ( it can be multiple times)
5. Action column - wherein actions are given like termination, warning letter
6. State name

Now, drop downs as below in my dashboard
1. date - from ( 1/1/2018) to end 1/3/2018
2. Employee name ( drop downs with state names n all )
3. State name ( drop down with state names n all)

I want output like , if suppose I select any employee n give start date n end date then for that specific period for that employee , how many unique employee got termination or warning letter ... this should changed when an user changes dates

pls guide....... I have been trying this since last week but unable to do it

 

[Rahul1987]

Emp Names	Dates	State	Action	Accused ID		Count Unique
Gigi	25-Jan-10​	UK	Termination	11111​		0​	Left side should be answer
Gigi	25-Jan-10​	UK	Termination	11111​		25-Feb-11​	Start Date Drop Down
Gigi	25-Jan-10​	UK	Termination	11111​		4-Mar-16​	End Date Drop Down
Don	25-Jan-10​	IND	Termination	11111​
Don	25-Jan-10​	IND	Warning letter	1112​		Country Dropdown	Names Drop down
Don	25-Jan-10​	IND	Warning letter	1112​		UK	GIGI
Don	25-Jan-10​	IND	Warning letter	1112​		IND	RAHUL
Don	25-Jan-10​	IND	Warning letter	573483​		AUS	ASHWIN
Rahul	3-Mar-16​	HK	Warning letter	573483​		SG	ATUL
Rahul	3-Mar-16​	HK	Warning letter	573483​		ALL	DON
Rahul	3-Mar-16​	HK	Shraddha	GER			ALL
Rahul	3-Mar-16​	HK	Shraddha	GER
Ashwin	3-Mar-16​	SG	Shraddha	GER
Ashwin	3-Mar-16​	SG	Shraddha	GER
Atul	3-Mar-16​	AUS	Shraddha	GER
Atul	3-Mar-16​	AUS	Shraddha	GER
Atul	3-Mar-16​	AUS	Shraddha	GER

 

[Rahul1987]

Eric W.xlsx
	A	B	C	D	E	F	G	H
2
3	Emp Names	Dates	State	Action	Accused ID		Count Unique
4	Gigi	25-Jan-10	UK	Termination	11111		0
5	Gigi	25-Jan-10	UK	Termination	11111		25-Feb-11	Start Date Drop Down
6	Gigi	25-Jan-10	UK	Termination	11111		4-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	3-Mar-16	HK	Warning letter	573483		SG	ATUL
13	Rahul	3-Mar-16	HK	Warning letter	573483		ALL	DON
14	Rahul	3-Mar-16	HK	Shraddha	GER			ALL
15	Rahul	3-Mar-16	HK	Shraddha	GER
16	Ashwin	3-Mar-16	SG	Shraddha	GER
17	Ashwin	3-Mar-16	SG	Shraddha	GER
18	Atul	3-Mar-16	AUS	Shraddha	GER
19	Atul	3-Mar-16	AUS	Shraddha	GER
20	Atul	3-Mar-16	AUS	Shraddha	GER
Unique

Cell Formulas
Range	Formula
G4	G4	=SUM(IF(FREQUENCY(IF(Criteria=$G$7,IF(Criteria2=$G$8,IF(Dates=">="&G5,Dates="<="&G6,MATCH(Names&Dates,Names&Dates,0)))),ROW(Names)-ROW(A4)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Criteria	=Unique!$D$4:$D$10	G4
Criteria2	=Unique!$E$4:$E$10	G4
Dates	=Unique!$B$4:$B$10	G4
Names	=Unique!$A$4:$A$10	G4

 

[Rahul1987]

Hi Eric ....Plsssssssssssssssss help ... I am 100% sure that you can help me out with this..

I do not need date to be mentioned in the formulae ., it should be via cell reference coz I am going to change dates very frequently.