Web Development

Ask something
Discussion Locked

Excel Distinct Count

0 helpful
7 replies

by chainsawz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!!

7 replies

by chainsawz

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 :>

by abailey101

A1:A5 is the range of your list

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

by chainsawz

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.

by dklippert

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.

This discussion is closed to new replies.