Add a CountDistinct (unique, etc.) function
Add it as a standalone function
+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.
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.
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.
I posted this on another page not realizing that others requested; thank you Liz for the link.
=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. Yancey commented
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 Barber commented
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:
Then you can use COUNT or whatever function on that.
For the IFS variants, we would need FILTER to be implemented:
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.
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!
Thomas K commented
Very nice to hear
Kevin Hanson commented
This would be great to have as a PivotTable summary option, too.
Andrew Engwirda commented
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".