Calculation of the number of unique values

Description

Sometimes in our work we need to count the unique values in a certain column, but Excel has functions that only sum up the number of records in a given field, for example the function COUNT() . The problem is that the same product or customer code can be repeated several times. But there is a way out, to solve our problem we can combine standard functions Excel. Let's see how to do it.

So let's combine the functions SUM() - sums up the meaning, IF() - condition check, FREQUENCY() - counts the number of values that fall into a certain interval, LEN() - counts the number of characters, MATCH() - searches for the position of an element in the array.

Calculation of the number of unique numerical values

=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1))

Counting the number of unique numeric and text values (does not work if there are empty cells)

=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))

Calculation of the number of unique values (universal formula)

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);"");IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);""))>0;1))

The last formula must be entered as an array formula, that is, it is not easy to click Enter , a Ctrl+Shift+Enter . After that, in the line of formulas, we will see that the formula is enclosed in curly brackets ( { } ), this is a sign that an array formula is entered.

Related Articles:

• Search for duplicates
• Duplicate removal
• Filtering unique values