Web Development·11,929 discussions

Excel Distinct Count

Locked

I have two columns in Excel: Location and MRN:

LOCATION MRN
Peds 125865
Broadway Clinic 125865
Inpatient 12563251
Broadway Clinic 12523652
Broadway Clinic 12523652

I need to count the MRN’s for each location. So, the result I need would look like this:

Broadway Clinic 3
Peds 1
Inpatient 1

Any ideas? Thanks!!

Topic

Reply To: Excel Distinct Count

In reply to Excel Distinct Count

I think you’ll find an answer here:

http://j-walk.com/ss/excel/tips/tip52.htm

(http://tinylink.com/?hB9mX0YR8N)

You might also use Data>Autofilter to display unique entries.

Reply To: Excel Distinct Count

In reply to Reply To: Excel Distinct Count

See below comment. This website did not provide the information for they type of count I am attempting to achieve. Thanks also for your quick response :>

Reply To: Excel Distinct Count

In reply to Excel Distinct Count

A1:A5 is the range of your list

=COUNTIF(A1:A5,”Peds”)
=COUNTIF(A1:A5,”Broadway Clinic”)
=COUNTIF(A1:A5,”Inpatient”)

Reply To: Excel Distinct Count

In reply to Reply To: Excel Distinct Count

My example did not post correctly. There are actually two distinct columns: Location and MRN. I need to count one column based on the contents of the other. So, for each MRN I need a count of the different locations, or vice versa. Thanks for the quick response and sorry for the confusion.

Reply To: Excel Distinct Count

In reply to Excel Distinct Count

I think you may be looking for a crosstab table or a pivot table.
The simple way is the hardest.
Place a list of the unique locations in, say cells G2:G4.
In cells H1:J1, enter the unique MRN ids.
We’ll assume the list/database is in the Range A1:B6.
A1=Location, B1=MRN
In cell H2 enter the formula:
=SUM(($A$2:$A$6=$G2)*($B$2:$B$6=H$1))
This is an array formula so enter it with Ctrl+Shift+Enter.
The formula will now look like this ? (the brackets can not be entered by hand):
{=SUM(($A$2:$A$6=$G2)*($B$2:$B$6=H$1))}
Fill the formula from H2 to J4

The harder way is easier.
Select the list/database.
Go to Data>Pivot Table
In the Layout portion of the wizard, drag Location to the Row area and MRN to the Column area.
Drag a second instance of MRN to the Data area
Double click on “Sum of MRN” and change it to Count.
Hit the Finish button.

Reply To: Excel Distinct Count

In reply to Reply To: Excel Distinct Count

Thanks again for your help!

Reply To: Excel Distinct Count

In reply to Excel Distinct Count

This question was closed by the author

[Author]

Replies