How to count unique values: Excel tutorial

How to count unique values: Excel tutorial

Who does not like it when formulas reduce the workload? Excel is a Microsoft software which is used by most people to prepare large data sets. It has a way of showing the formula of a particular value with minimal efforts. It has been revamped over the years to make it more appealing to its users. You can now count unique values: Excel due to the improvements made by Microsoft.

How to count unique values in Excel

Source: UGC

While preparing some of your data sets, there might be those values or texts that appear more than once. The repetition, in most cases, makes it hard for you to have an accurate number of the values or texts in the list. There are various methods to count unique values in the program which will make it easier for you to get a conclusive count of your data.

How to count unique values Excel with the advanced filter method

This method is straightforward and no formula is required. You can follow the following guideline to find unique values in Excel:

  1. Begin by selecting any cell from the list.
  2. On your toolbar, click the data icon and press advanced.
  3. Once you click on it, you will get a pop-up window to apply advanced filters.
  4. On the advanced apply filters, it will show you different ways of using them.
  5. Still, on advanced, choose “copy to another location.”
  6. Below it, on “copy to”, pick a blank cell. This will help identify where you want unique values to be copied.
  7. On that advanced dialogue box, click “unique records only”, then press OK.

The list of the distinctive values will now be well arranged at this point; you can thus go ahead and type =COUNTA(B2-B10) on the last cell. The unique values and their count is, therefore, in place. It is an easy Excel count unique values method.

READ ALSO: How to insert PDF into Word: a simple tutorial

The SUM and COUNTIF combination

find unique values in Excel

Source: UGC

You can use this combination formula if you intend to find the count of Excel unique values in a single cell without having a separate list. The formula identifies the Excel list unique values in which you should use the Ctrl+Shift+Enter while entering the array formula:

=SUM (1/COUNTIF (A2:A17, A2:A17))

This formula has three crucial parts which enable you to get your last result:

  1. The COUNTIF on the formula is used to count the number of values from 16 has a way in which it displays: =SUM(1/{1;2;2;2;1;1;2;2;2;1;2;2;2;2;2;2})
  2. There is also a part of the formula where you have divided all the values by one SUM(1/COUNTIF). The formula =SUM ({1;0.5;0.5;0.5;1.1;0.5;0.5;0.5;1;0.5;0.5;0.5;0.5,0.5,0.5}) enables the count to add up to one if the value has appeared twice.
  3. The sum function, on the other hand, plays a role in counting all Excel unique values in range. This method is beneficial and accurate to get your Excel list unique values.


Excel unique values

Source: UGC

This method on how to count unique values in Excel can also be used to easily find unique values. The use of sum-product in this formula (=SUMPRODUCT (1/COUNTIF (A2: A17, A2: A17)) means you do not have to put it in an array. It can take an array without using Ctrl+Shift+Enter.

READ ALSO: How to recover unsaved Word document

Counting unique texts only

In the case where you have a list that contains numbers and texts, you can consider this formula if you want Excel count unique text only:

=SUM (IF (ISTEXT (A2:A17),1/COUNTIF (A2:A17, A2:A17),""))

It is the formula in which each function used plays a particular role.

The ISTEXT verifies if all the values are in the text or not. It then displays “true” if it is a text. The IF function, on the other hand, applies COUNTIF on all values where there is “true”, and the rest are left blank. The SUM function, in the end, gives you the count of all the text values.

Imagining cases where you may have entered duplicate values and texts can make the process of spreadsheet entry tedious. Learning how to count unique values: Excel is thus important as it will help you in preventing duplication of work when keying in the values. Other advantages of learning the process are that it saves time and enables you to attain more accurate entries when using Ms Excel.

READ ALSO: Superscript and subscript in Word: tips and shortcuts

Source: Legit

Mailfire view pixel