Count only Unique criteria

[mduntley]

Hello,

I am trying to figure out a IF formula to say how may criteria mach with some codes. I have made a simple graph. Goal is to find a patient with two Codes specific codes, which is 96365 and J0984

[TABLE="class: grid, width: 319"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD]Patient ID[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]1[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]2[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]2[/TD]
[TD]J0984[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]4[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]3[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]3[/TD]
[TD]J0984[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]5[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]6[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]6[/TD]
[TD]J0984[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]7[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]7[/TD]
[TD]J0987[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]9[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]10[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]10[/TD]
[TD]J0984[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]11[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]12[/TD]
[TD]96365[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD]12[/TD]
[TD]J0984[/TD]
[/TR]
</tbody>[/TABLE]


Results that i want.
[TABLE="class: grid, width: 132"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Hospital 1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Hospital 2[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


I have tried to use counties, but with that it will count each rows, which will make hospital 1 to have 4, when i want to see how many patients, so we see 2 patients match this criteria

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C
1	Location	Patient ID	Code
2	Hospital 1	1	96365
3	Hospital 1	2	96365
4	Hospital 1	2	J0984
5	Hospital 1	4	96365
6	Hospital 1	3	96365
7	Hospital 1	3	J0984
8	Hospital 1	5	96365
9	Hospital 2	6	96365
10	Hospital 2	6	J0984
11	Hospital 2	7	96365
12	Hospital 2	7	J0987
13	Hospital 2	9	96365
14	Hospital 2	10	96365
15	Hospital 2	10	J0984
16	Hospital 2	11	96365
17	Hospital 2	12	96365
18	Hospital 2	12	J0984
19
20
21	Hospital 1	2
22	Hospital 2	3
Sheet

 

[mduntley]

That is good, but it does not reference the patient column., if you change the code for hospital 2 patient 7 to J0984, the value for hospital 2 will go down to 2 when it should go to 4