General discussion

  • Creator
    Topic
  • #2297016

    Excel Distinct Count

    Locked

    by chainsawz ·

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

All Comments

  • 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

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

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

      • #2684759

        Reply To: Excel Distinct Count

        by chainsawz ·

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

    • #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,”Broadway Clinic”)
      =COUNTIF(A1:A5,”Inpatient”)

      • #2684760

        Reply To: Excel Distinct Count

        by chainsawz ·

        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.

    • #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

Viewing 3 reply threads