How can we improve Excel for Windows (Desktop Application)?

Add a CountDistinct (unique, etc.) function

Add it as a standalone function

66 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jorge S.Jorge S. shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    9 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • KERATLKERATL commented  ·   ·  Flag as inappropriate

        +1 to Charles' CreateList with parameters for (a) unique vs dupes, and (b) leave original sort vs. sort by alpha, or any other supported sorts.
        TBD:
        Would it be an array formula? Since the user wouldn't know how large the array is, would they enter it in just one cell and it would autoconvert to an array? If so, then it would have to include warnings if it would overwrite existing cells, just like a pivottable. Would it be fixed or dynamic? I could see issues if changes to the source list ends up then extending the unique list (or list of dupes) into a used range, so maybe this is a fixed formula that autoconverts the range into a list, and does not store the formula? If someone needed to refresh it they would have to use VBA to re-push the formula to the cell.
        Also, it would help to have another parameter for horizontal vs vertical output; maybe default to the orientation of the source data, but allow this parameter to override, saving the user from having to transpose as a separate step.

      • CharlesCharles commented  ·   ·  Flag as inappropriate

        In the same spirit a CreateList function would very very useful. It would take a range as an input, and would return an array containing a sorted list of the unique occurences of each value in the input range.

      • Anonymous commented  ·   ·  Flag as inappropriate

        I posted this on another page not realizing that others requested; thank you Liz for the link.

        (http://answers.microsoft.com/en-us/office/forum/office_2010-excel/create-a-function-distinct/3a7a3697-ec1b-48f9-b64f-15d0a409e06b)

        =distinct() or =countdistinct() need not be an array; it can be a straight formula that would count the amount of unique numbers.

        =duplicate can be the opposing function.

        It is so basic; let's make life easier than these complicated multi layer functions currently required to get this done.

      • Denise K. YanceyDenise K. Yancey commented  ·   ·  Flag as inappropriate

        I wanted to see how many employees had certain certificates - knowing each employee had several certificates - so I did a pivot table, found out which employees had each certificate, but couldn't get Excel to give me a simple total of how many employees had each one of the certificates, because I couldn't get it to understand to count each employee only once, unique. This might help! Please bring it out soon!

      • Kenneth BarberKenneth Barber commented  ·   ·  Flag as inappropriate

        I can see some problems with this already. As Andrew already pointed out, he wants COUNTDISTINCTIFS. I can see others wanting SUMDISTINCT or AVERAGEDISTINCT with IFS variants. That's just too many functions.

        I think that the UNIQUE function suggestion should be implemented instead:
        https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9095341-create-function-unique-array-extracting-unique-va

        Then you can use COUNT or whatever function on that.

        For the IFS variants, we would need FILTER to be implemented:
        https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10886142-array-saving-filter-function-to-replace-vlookup

        The downside of my suggestion is that you always need 2 functions, but the upside is that it's better long-term. Microsoft is only ever going to add functions to Excel, so we don't want future versions having a long list of functions that are slight variants of each other. It would be intimidating and painful to sift through.

      • MargaretMargaret commented  ·   ·  Flag as inappropriate

        Yes together with the PivotTable summary option as suggested by Kevin Hanson, this would make an excellent addition to Excel. Can't wait to use it!

      • Andrew EngwirdaAndrew Engwirda commented  ·   ·  Flag as inappropriate

        And also COUNTDISTINCTIFS.

        For example, =COUNTDISTINCTIFS(A:A,B:B,"x",C:C,"y") means count distinct values in Column A, if values in Column B are "x" and values in Column C are "y".

      Feedback and Knowledge Base