How to count unique values ​​in Excel

How to quickly count values ​​in Excel

Do you need a way to quickly count the number of values ​​in an Excel data set? To be clear - this is the total of values, not the unique value.

If you just need to know the numbers (but don't want to interact with them in a spreadsheet), there's a quick way to check. To do this, select the cell in the spreadsheet that contains your value.

With the cell selected, look at the bottom of the Excel window (below the worksheet tab). You'll see different calculations - including the value count. This counts the number of non-empty cells.

While this does not allow you to count the number of unique values, it does provide a quick way to check how many of your cells have a value versus none. Cell.

How to use SUMPRODUCT and COUNTIF to calculate unique values ​​in Excel

If you want to calculate unique values ​​in Excel, the best way is to use a formula. You can do this using a combination of the SUM and COUNTIF functions.

To quickly explain what these functions do, SUMPRODUCT will multiply and then return the sum of the data ranges. COUNTIF will count the number of cells in the range that match the condition. You can use both functions together to return the number of unique values ​​for each cell in an array.

For example, if you have a list of numbers from 1 to 8, but 8 is repeated twice, the returned array will contain the value 1 — except where 8 is listed, it will return 2. That's because the value 8 is repeated twice.

Use SUMPRODUCT and COUNTIF to count unique values ​​in Excel:

1. Open your Excel spreadsheet.
2. Select an empty cell next to the data set and make sure the column it is in is empty.
3. In the edit bar, type the following formula: =SUMPRODUCT(1/COUNTIF(range,range)), and replace the two range values ​​with the included data range of cells. (For example =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))).
4. Press Enter — The formula will return the total number of unique values ​​in the range of cells.
   

How to calculate unique values ​​in Excel using PivotTable

Another way to calculate unique values ​​in Excel isUsing PivotTable surface. This allows you to analyze the data more closely.

To calculate unique values ​​in Excel using a PivotTable:

1. Open your Excel spreadsheet.
2. Select your data.
3. Press Insert > PivotTable.
   
4. The data range you selected should already appear as a condition in the PivotTable options window. Choose whether you want to add the PivotTable to a new worksheet or existing worksheet.
5. Make sure the Add this data to data model checkbox is selected.
6. Press OK to insert the pivot table.
   
7. In the PivotTable Fields menu, select the column that contains the data you want to analyze.
8. Under Value, press the existing option and select Value Field Settings.
   
9. Scroll through the list and select the Distinct Count option.
10. Press to confirm.
    

The PivotTable will update to show the total number of unique values ​​in the dataset.

You can configure the PivotTable to further change this setting. For example, you might want to break this value down by using subcategories, depending on how your data is laid out in your spreadsheet.

Calculating Values ​​in Microsoft Excel

Using the steps above, you can quickly calculate unique values ​​in Excel. This is just one way you can use Excel to calculate, manipulate, and analyze different values. For example, you might want to use Excel to calculate a percentage increase or decrease, which is perfect for your financial planning.

The above is the detailed content of How to count unique values ​​in Excel. For more information, please follow other related articles on the PHP Chinese website!