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.

 

[CA_Punit]

I am a bit confused with your Expected Answer. Do you want your expected answer to be this

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

 

[montecarlo2012]

is about:

• SELECT DISTINCT returns only distinct (i.e. different) values.
• SELECT DISTINCT eliminates duplicate records from the results.
• DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.
• DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.

so can you point here, if you don't mind
then maybe you got help

 

[montecarlo2012]

Distinct here is the block on the wall.
so you are looking for

1​	different,
2​	disparate,
3​	dissimilar,
4​	distant,
5​	distinctive,
6​	distinguishable,
7​	diverse,
8​	nonidentical,
9​	other,
10​	unalike,
11​	unlike

 

[Fluff]

@shellp
What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.

 

[shellp]

CA_Punit - sorry, no, I want totals for the periods as stated. I have the number of visits in my table for other reasons but should have excluded it for this example.
Fluff - I am using Excel 2010, thanks.
Montecarlo1212 - I am looking for distinct HCN based on: fiscal year, MIS, and treating hospital. As with CA_Punit, I shouldn't have included the visits column, I am looking for a summary formula on another worksheet from the above which is "raw data"

thanks to you all.

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F
1	Fyear	MIS	Rescode	Treating_Hospital	HCN	Visits
2	2018	Dialysis_Home	240	Grey-Sloane	5555555555	2
3	2018	Dialysis_Home	240	Grey-Sloane	4444444	2
4	2018	Dialysis_Other	200	Grey-Sloane	4444444444	1
5	2018	Dialysis_Other	232	Sunshine Hospital	5555555555	1
6	2018	Dialysis_Other	240	Grey-Sloane	5555555555	1
7	2019	Dialysis_Home	200	Sunshine Hospital	4444444444	1
8	2019	Dialysis_Home	232	Grey-Sloane	5555555555	1
9	2019	Dialysis_Home	240	Grey-Sloane	5555555555	1
10	2019	Dialysis_Other	200	Grey-Sloane	5555555555	1
11	2019	Dialysis_Other	240	Sunshine Hospital	5555555555	1
Main

Cell Formulas
Range	Formula
F2:F11	F2	=SUM(--(FREQUENCY(IF($A$2:$A$11=A2,IF($B$2:$B$11=B2,IF($C$2:$C$11=C2,IF($D$2:$D$11=D2,MATCH($E$2:$E$11,$E$2:$E$11,0))))),ROW($E$2:$E$11)-ROW($E$2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

PS. Don't forget to update your account details to show your version of Excel & then scroll down & click save.

 

[CA_Punit]

Try This

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Fyear	MIS	Rescode	Treating_Hospital	HCN	Visits								Disctinct COUNT
2	2018	Dialysis_Home	240	Grey-Sloane	abc	1				2018	Dialysis_Home	240	Grey-Sloane	1
3	2018	Dialysis_Home	240	Grey-Sloane	abc	1								Enter with Control Shift & Enter
4	2018	Dialysis_Home	240	Grey-Sloane	abc	1
5	2018	Dialysis_Other	232	Sunshine Hospital	abc	1
6	2018	Dialysis_Other	240	Grey-Sloane	abc	1
7	2018	Dialysis_Home	240	Grey-Sloane	abc	1
8	2019	Dialysis_Home	232	Grey-Sloane	abc	1
9	2019	Dialysis_Home	240	Grey-Sloane	abc	1
10	2019	Dialysis_Other	200	Grey-Sloane	abc	1
11	2019	Dialysis_Other	240	Sunshine Hospital	abc	1
12
13
Sheet1

Cell Formulas
Range	Formula
N2	N2	=SUM(IFERROR(--(MATCH(MATCH(IF(($A$2:$A$11=J2)*($B$2:$B$11=K2)*(C2:C11=L2)*(D2:D11=M2),E2:E11),$E$2:$E$11,0),ROW($E$2:$E$11)-ROW(E2)+1,0)/(ROW($E$2:$E$11)-ROW(E2)+1)=1),0))

 

[CA_Punit]

There was a mistake in the formula

Try this
=SUM(IFERROR(FREQUENCY(IFERROR(MATCH(MATCH(IF(($A$2:$A$11=J2)*($B$2:$B$11=K2)*(C2:C11=L2)*(D2:D11=M2),E2:E11),$E$2:$E$11,0),ROW($E$2:$E$11)-ROW(E2)+1,0),FALSE),ROW($E$2:$E$11)-ROW(E2)+1)^0,0))

 

[shellp]

Thanks for everyone's assistance. CA_Punit, unfortunately your formula doesn't seem to work. Fluff's works but I need to keep testing to see about speed - there are 44545 rows in the raw data sheet just doing one rescode took some time so adding others may impact it too much to use this method.