Add a CountDistinct (unique, etc.) function
Add it as a standalone function
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".