+ Reply to Thread
Results 1 to 12 of 12

How can I count unique items in a filtered column?

  1. #1
    Registered User
    Join Date
    07-06-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    How can I count unique items in a filtered column?

    Hi,

    I have a column with patients names like this:

    smith
    taylor
    taylor
    taylor
    garcia
    garcia
    williams
    brent

    I need to count the duplicates only once though (to equal 5 in this example). It's a pretty massive spreadsheet I'll be applying different filters to. I'm not advanced in Excel, so I'll need it explained in lamen's terms.

    Thanks in advance,
    Terry

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: How can I count unique items in a filtered column?

    Refer attached.

    The count is displayed in Column B and total no. of unique names is in Cell C1
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How can I count unique items in a filtered column?

    Without the helper column

    To count all unique records

    =SUM(IF(A2:A9<>"",1/COUNTIF(A2:A9,A2:A9)))

    To count filtered unique records

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10)-ROW(A2),0)),MATCH(A2:A10&"",A2:A10&"",0)),MATCH(A2:A10&"",A2:A10&"",0)),1))

    Note that these are both array formula and must be confirmed using Shift Ctrl Enter.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How can I count unique items in a filtered column?

    count with pivot table without helpcell (see the attachment).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    07-06-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How can I count unique items in a filtered column?

    Thank you so much for your responses! I'll definitely try these out today and write back on which one I end up using.

  6. #6
    Registered User
    Join Date
    07-06-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How can I count unique items in a filtered column?

    Quote Originally Posted by jason.b75 View Post
    Without the helper column

    To count all unique records

    =SUM(IF(A2:A9<>"",1/COUNTIF(A2:A9,A2:A9)))

    To count filtered unique records

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10)-ROW(A2),0)),MATCH(A2:A10&"",A2:A10&"",0)),MATCH(A2:A10&"",A2:A10&"",0)),1))

    Note that these are both array formula and must be confirmed using Shift Ctrl Enter.
    Okay. I have a few questions. Like I said, I'm still learning. How much of this information will I be changing to fit my table?
    Would I have to alter the formula for filtered data each time i apply a different filter?

  7. #7
    Registered User
    Join Date
    07-06-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How can I count unique items in a filtered column?

    I seem to have gotten this to work. Is there any way I could use this to work within filters?

    =SUMPRODUCT(1/COUNTIF(R[-1773]C[-21]:R[-3]C[-21],R[-1773]C[-21]:R[-3]C[-21]))

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How can I count unique items in a filtered column?

    why the rc format?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How can I count unique items in a filtered column?

    Quote Originally Posted by terry_believers View Post
    Would I have to alter the formula for filtered data each time i apply a different filter?
    The second formula from my previous post will only count the rows still visible after the filter is applied and will recalculate when you change the filter criteria.

    To apply to your table, A2:A10 should be changed to refer to your entire range of data, A2 should be the first cell.

    Using the R1C1 notation from your formula:-

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(R[-1773]C[-21],ROW(R[-1773]C[-21]:R[-3]C[-21])-ROW(R[-1773]C[-21]),0)),MATCH(R[-1773]C[-21]:R[-3]C[-21]&"",R[-1773]C[-21]:R[-3]C[-21]&"",0)),MATCH(R[-1773]C[-21]:R[-3]C[-21]&"",R[-1773]C[-21]:R[-3]C[-21]&"",0)),1))

    Remember to array confirm the formula with Shift Ctrl Enter.

  10. #10
    Registered User
    Join Date
    07-06-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How can I count unique items in a filtered column?

    I suppose that's my default setting. Is there a quick way to change that?

  11. #11
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: How can I count unique items in a filtered column?

    ALT > F > I > F > Working with Formulas > R1C1 Reference Style (uncheck this option)
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  12. #12
    Registered User
    Join Date
    07-06-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How can I count unique items in a filtered column?

    Fantastic! I've changed my settings to match the A2:A10 format and I've inputted that formula to count filtered unique records. It works like a charm. Thank you guys SO much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1