# Web Development

## General discussion

• Creator
Topic
• #2297016

### Excel Distinct Count

Locked

by chainsawz ·

I have two columns in Excel: Location and MRN:

LOCATION MRN
Peds 125865
Inpatient 12563251

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

Peds 1
Inpatient 1

Any ideas? Thanks!!

• Author
Replies
• #2685105

### Reply To: Excel Distinct Count

by dklippert ·

In reply to Excel Distinct Count

I think you’ll find an answer here:

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

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

• #2684759

### Reply To: Excel Distinct Count

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

• #2685103

### Reply To: Excel Distinct Count

by abailey101 ·

In reply to Excel Distinct Count

A1:A5 is the range of your list

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

• #2684760

### Reply To: Excel Distinct Count

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.

• #2684739

### Reply To: Excel Distinct Count

by dklippert ·

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.

• #2673258

### Reply To: Excel Distinct Count

by chainsawz ·

In reply to Excel Distinct Count

This question was closed by the author