# Web Development

## General discussion

Locked

### Excel Distinct Count

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

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)
Thread display: Collapse - | Expand +

Collapse -
by 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.

Collapse -

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

Collapse -
by In reply to Excel Distinct Count

A1:A5 is the range of your list

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

Collapse -

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.

Collapse -
by 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.

Collapse -

Collapse -
by In reply to Excel Distinct Count

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)

## Related Discussions

• 16

• 9

• 1

#### Bloggers

matthewjohn938 ·

• 0

#### XML FEEDS for Real Estate

info1585583543 ·

• 8

hsingh91 ·