How to Count Unique Values in Excel


Excel

Looking to discover the variety of distinctive values in an Excel information set? You can rely distinctive values in Excel in just a few methods. This information will clarify how.

It may be tough taking a look at an Excel spreadsheet. The values can all merge collectively into one, making it tough to discover the information you’re searching for.

That’s why it’s essential to know how one can analyze your information. For instance, you may want to discover what number of repeating numbers there are in a cell vary—or what number of distinctive numbers there are as a substitute.

If you need to know the way to rely distinctive values in Excel, comply with the steps beneath.

How to Quickly Count Values in Excel

Do you want a approach to rapidly rely the variety of values which might be in a knowledge set in Excel? To be clear—that is the complete variety of values, not distinctive values.

If you solely want to know the quantity (however don’t need to work together with it in your spreadsheet), there’s a fast approach to verify. To do that, choose the cells containing your values in your spreadsheet.

With the cells chosen, check out the underside of your Excel window (beneath the sheet labels). You’ll see completely different calculations for you—together with the worth rely. This counts the variety of cells that aren’t empty.

Quickly counting the values in a cell range in Excel

While this doesn’t enable you to rely the variety of distinctive values, it does provide a fast approach to verify what number of of your cells have a worth versus those that don’t.

How to Count Unique Values in Excel Using SUMPRODUCT and COUNTIF

If you need to rely distinctive values in Excel, one of the best ways to do it’s to use a system. You can use a mixture of the SUM and COUNTIF features to do that.

To rapidly clarify what these features do, SUMPRODUCT will multiply after which return the sum of a knowledge vary. COUNTIF will rely the variety of cells in a variety that match a situation. You can use each features collectively to return the variety of distinctive values for every cell in an array.

For instance, in case you’ve bought an inventory of numbers starting from one to eight, however eight is repeated twice, the array returned will include the worth 1—aside from the place eight is listed, the place it’ll return 2. That’s as a result of the worth 8 is repeated twice.

To rely distinctive values in Excel utilizing SUMPRODUCT and COUNTIF:

  1. Open your Excel spreadsheet.
  2. Select an empty cell subsequent to your information set, guaranteeing that the column it resides in is empty.
  3. In the system bar, sort the next system: =SUMPRODUCT(1/COUNTIF(vary,vary)) whereas changing each vary values with the cell vary containing your information. (eg. =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))).
  4. Press Enter—the system will return the whole variety of distinctive values in the cell vary.
    Calculating the total number of unique values in a cell range in Excel

How to Count Unique Values in Excel Using a Pivot Table

Another approach to rely the distinctive values in Excel is to use a pivot desk. This permits you to analyze your information extra intently.

To rely distinctive values in Excel utilizing a pivot desk:

  1. Open your Excel spreadsheet.
  2. Select your information.
  3. Press Insert > Pivot Table.
    Inserting a pivot table in Excel
  4. Your chosen information vary ought to already seem as the standards in the Pivot Table choices window. Select whether or not you need to add the pivot desk to a brand new worksheet or an present worksheet.
  5. Make positive to choose the Add this information to the Data Model checkbox.
  6. Press OK to insert your pivot desk.
    Setting up your pivot table options in Excel
  7. In the PivotTable Fields menu, choose the column containing the information you need to analyze.
  8. Under Values, press the prevailing choice and select Value Field Settings.
    Setting up a pivot table in Excel
  9. Scroll by the checklist and choose the Distinct Count choice.
  10. Press OK.
    Setting up a pivot table in Excel

The Pivot Table will replace to present you the whole variety of distinctive values in your information set.

Using a pivot table to calculate the number of unique values in an Excel data set

You can configure your Pivot Table to change this additional. For instance, it’s possible you’ll want to break this worth down through the use of sub-categories, relying on how your information is laid out in your spreadsheet.

Calculating Values in Microsoft Excel

Using the steps above, you may rapidly rely distinctive values in Excel. This is only one approach you should utilize Excel to calculate, manipulate, and analyze completely different values. For instance, you may want to calculate proportion will increase or decreases utilizing Excel—good in your monetary planning.

There are different formulation in Excel that you should utilize, from important formulation like SUM to extra superior formulation like TRUNC. If you’re new to Excel, don’t neglect to try these important Excel ideas to get began.