Counting the number of unique values

Formulation of the problem

There is a data range in which some values ​​are repeated more than once:

Counting the number of unique values

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:

Counting the number of unique values

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:

Counting the number of unique values

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:

Counting the number of unique values

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):

Counting the number of unique values

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

 

Leave a Reply