Count only Unique criteria

mduntley

Board Regular
Joined
May 23, 2015
Messages
67
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

LocationPatient IDCode
Hospital 1196365
Hospital 1296365
Hospital 12J0984
Hospital 1496365
Hospital 1396365
Hospital 13J0984
Hospital 1596365
Hospital 2696365
Hospital 26J0984
Hospital 2796365
Hospital 27J0987
Hospital 2996365
Hospital 21096365
Hospital 210J0984
Hospital 21196365
Hospital 21296365
Hospital 212J0984

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


Results that i want.
Hospital 12
Hospital 23

<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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.
Excel Workbook
ABC
1LocationPatient IDCode
2Hospital 1196365
3Hospital 1296365
4Hospital 12J0984
5Hospital 1496365
6Hospital 1396365
7Hospital 13J0984
8Hospital 1596365
9Hospital 2696365
10Hospital 26J0984
11Hospital 2796365
12Hospital 27J0987
13Hospital 2996365
14Hospital 21096365
15Hospital 210J0984
16Hospital 21196365
17Hospital 21296365
18Hospital 212J0984
19
20
21Hospital 12
22Hospital 23
Sheet
 

mduntley

Board Regular
Joined
May 23, 2015
Messages
67
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,819
Members
414,179
Latest member
anthelsene

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top