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

Location	Patient ID	Code
Hospital 1	1	96365
Hospital 1	2	96365
Hospital 1	2	J0984
Hospital 1	4	96365
Hospital 1	3	96365
Hospital 1	3	J0984
Hospital 1	5	96365
Hospital 2	6	96365
Hospital 2	6	J0984
Hospital 2	7	96365
Hospital 2	7	J0987
Hospital 2	9	96365
Hospital 2	10	96365
Hospital 2	10	J0984
Hospital 2	11	96365
Hospital 2	12	96365
Hospital 2	12	J0984

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

Results that i want.

Hospital 1	2
Hospital 2	3

<colgroup><col><col></colgroup><tbody>
</tbody>

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