Contents
Formulation of the problem
There is a data range in which some values are repeated more than once:
The task is to count the number of unique (non-repeating) values in the range. In the example above, it is easy to see that only four options are actually mentioned.
Let’s consider several ways to solve it.
Method 1. If there are no empty cells
If you are sure that there are no empty cells in the original data range, then you can use the short and elegant array formula:
Don’t forget to enter it as an array formula, i.e. press after entering the formula not Enter, but the combination Ctrl + Shift + Enter.
Technically, this formula iterates through all the cells of the array and calculates for each element the number of its occurrences in the range using the function COUNTIF (COUNTIF). If we represent this as an additional column, then it would look like this:
Then the fractions are calculated 1/Number of occurrences for each element and they are all summed up, which will give us the number of unique elements:
Method 2. If there are empty cells
If there are empty cells in the range, then you will have to slightly improve the formula by adding a check for empty cells (otherwise we will get a division error by 0 in a fraction):
That’s it.
- How to extract unique elements from a range and remove duplicates
- How to highlight duplicates in a list with color
- How to compare two ranges for duplicates
- Extract unique records from a table by a given column using the PLEX add-on