Distinct Count a Column Based on Criteria from Other Columns

[shellp]

Fyear	MIS	Rescode	Treating_Hospital	HCN	Visits
2018	Dialysis_Home	0240	Grey-Sloane	5555555555	1​
2018	Dialysis_Home	0240	Grey-Sloane	5555555555	1​
2018	Dialysis_Other	0200	Grey-Sloane	4444444444	1​
2018	Dialysis_Other	0232	Sunshine Hospital	5555555555	1​
2018	Dialysis_Other	0240	Grey-Sloane	5555555555	1​
2019	Dialysis_Home	0200	Sunshine Hospital	4444444444	1​
2019	Dialysis_Home	0232	Grey-Sloane	5555555555	1​
2019	Dialysis_Home	0240	Grey-Sloane	5555555555	1​
2019	Dialysis_Other	0200	Grey-Sloane	5555555555	1​
2019	Dialysis_Other	0240	Sunshine Hospital	5555555555	1​

For the table above, I'd like to find distinct count of the HCN field (unique identifier for patient) with the filters of fyear, MIS, rescode and treating hospital. So for the above, if the fiscal year is 2018, the MIS is dialysis_home, the rescode is 0240 and the treating hospital is Grey-Sloane, there should be 1 distinct patient.

I don't know how to link other posts but in an August 17, 2020 post about distinct counts based on patient IDs, Eric W responded with =SUM(SIGN(FREQUENCY(IF(C2:C6=G2,IF(D2:D6=H2,E2:E6)),E2:E6))) where E was the patient identifier. This works for my example if the HCN is a number field but in my case it is not so can Eric W's response be edited for use with text? Thanks for any and all assistance.

 

[shellp]

Sorry guys, the array plus the size of the dataset is causing huge delays...is there any other way to do this? Thanks.

 

[Fluff]

Thanks for updating your profile
Unfortunately, I don't know of any formula that would be faster & would work with 2010.

 

[CA_Punit]

It is working Fine

Book1
	A	B	C	D	E	F	G
1	Fyear	MIS	Rescode	Treating_Hospital	HCN	Visits
2	2018	Dialysis_Home	240	Grey-Sloane	abc	1	2
3	2018	Dialysis_Home	240	Grey-Sloane	def	1	2
4	2018	Dialysis_Home	240	Grey-Sloane	abc	1	2
5	2018	Dialysis_Other	232	Sunshine Hospital	abc	1	2
6	2018	Dialysis_Other	232	Sunshine Hospital	def	1	2
7	2018	Dialysis_Home	240	Grey-Sloane	abc	1	2
8	2019	Dialysis_Home	232	Grey-Sloane	abc	1	1
9	2019	Dialysis_Home	240	Grey-Sloane	abc	1	1
10	2019	Dialysis_Other	200	Grey-Sloane	abc	1	1
11	2019	Dialysis_Other	240	Sunshine Hospital	abc	1	1
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=SUM(IFERROR(FREQUENCY(IFERROR(MATCH(MATCH(IF(($A$2:$A$11=A2)*($B$2:$B$11=B2)*($C$2:$C$11=C2)*($D$2:$D$11=D2),$E$2:$E$11),$E$2:$E$11,0),ROW($E$2:$E$11)-ROW($E$2)+1,0),FALSE),ROW($E$2:$E$11)-ROW($E$2)+1)^0,0))

 

[CA_Punit]

So if we use Pivot Table to count Unique and then Vlookup with all critieria using "&" it should work fast

 

[shellp]

Thanks Fluff, that is what I ended up doing.