**Counting all distinct values in a range** in Excel is useful when working with **large sets of data** that contain duplicate values. By identifying unique values, you can gain insights into the data and perform analysis more accurately. Excel provides several **built-in functions** to count unique or distinct values, such as **COUNTIF, SUMIF,** and **COUNTIFS**. However, these functions only count unique values based on a single criterion. It would help if you used a different formula to count all distinct values in a range regardless of any criteria. The formula for counting all distinct values in a range in Excel is based on the **SUM** and **COUNTIF** functions. It uses an array formula to calculate the count of unique values in the range. Excel also provides another way to **count all Distinct Values** **in a Range by Kutools**.

Using this formula and Kutools, you can **quickly** and **easily** count all unique values in a range without the need for manual data manipulation. It can save you time and improve the accuracy of your analysis. Let us see in this article how to count all distinct values in various methods.

## Jump to

## Pre-requirements

Here are some important notes to keep in mind when counting all distinct values in a range in Excel:

- The formula for counting all distinct values in a range only works for values that are
**numbers, text, or dates**. It will not work for other data types, such as**formulas or errors**. - The formula
**does not count blank cells**in the range. You must modify the formula if you want to include blank cells in the count. - The formula for counting all distinct values in a range is an array formula. You must enter it as an array formula by pressing
**Ctrl + Shift + Enter**instead of**Enter**. - If you add or remove values from the range, you need to recalculate the formula by pressing
**F9**or by editing and re-entering the formula. - The formula for counting all distinct values in a range can be slow to calculate for very large ranges. In this case, you may consider using other techniques, such as a
**PivotTable**or a**VBA macro**.

By keeping these important notes in mind, you can use the formula for counting all distinct values in a range in Excel **effectively** and **accurately**.

## Steps to Count All Distinct Values in a Range

- To count all distinct values in a range in Excel, you can use the following formula:

=SUM(1/COUNTIF(range,range))

- Replace “
**range**” with the actual range of cells that you want to count the distinct values from.

Here’s how to use the formula:

**Select**a**blank cell**where you want to display the result.

- Enter the formula
**=SUM(1/COUNTIF(range, range))**in the formula bar.

- Replace “
**range**” with the actual range of cells that you want to count the distinct values from. - After that, you have to press
**Enter**.

- The formula will count all the unique values in the specified range and return the result. Note that this formula only works for values that are numbers, text, or dates and does not include any blank cells in the range.

## Steps to Count All Distinct Values in a Range using Kutools

To **Count All Distinct Values in a Range** in Excel, do as follows.

For example, enter the names in a Column. Enter the names (**first duplicate values**) you want to count in another column.

**Select**the**Blank Cell**to get the Output.- On the
**Kutools**tab, Select**Formula Helper**icon, and it will display the menu. - From that menu, Select
**Formula Helper**option.

- It will open the
**Formula Helper Dialog box**. In that Dialog box, you must**Count cells with unique values (including the first duplicate)**option from the Choose formula box. - In the
**Range**box, Select the range of cells you want to count the distinct values. - Hit the
**Ok**button.

- Now, it will display the distinct count in the output box.

From the above image, you can see the **distinct count** of the range of cells displayed properly.

## Advantages of Count All Distinct Values in a Range

There are several advantages to counting all distinct values in a range in Excel:

**Improved decision-making**: By identifying unique values, you can gain insights into the data and make more informed decisions.**Time-saving**: The formula for counting all distinct values in a range in Excel is easy to use and can save you time when working with large data sets.**Automatable**: The formula for counting all distinct values in a range in Excel can be automated using a macro, allowing you to streamline your data analysis process.**Flexibility**: The formula for counting all distinct values in a range in Excel works for both numeric and non-numeric values, making it a flexible tool for working with various data types.**Accurate analysis**: Counting all distinct values in a range can provide a more accurate analysis of your data as it eliminates any duplicate values.

Counting all distinct values in a range in Excel can help you better understand your data, improve your analysis, and make more informed decisions.

## Verdict

In this article, **counting all distinct values in a range in Excel** is useful for analyzing and gaining insights from large data sets. Excel provides several **built-in functions** for counting unique or distinct values. Using this formula, you can easily count all unique values in a range without manually manipulating the data. It can **save** you **time and improve the accuracy of your analysis**, allowing you to make more informed decisions based on your data.

## Video Tutorial

Here is the video tutorial for your better understanding.

## FAQ

**Can the COUNTIF function count distinct values in Excel?**

The **COUNTIF** function can count distinct values in Excel, but only based on a single criterion. It would help if you used a different formula to count all distinct values in a range regardless of any criteria.

**What does the formula “=SUM(1/COUNTIF(range,range))” mean?**

This formula uses an array formula to calculate the count of unique values in a range. It works by first calculating the count of each value in the range using the **COUNTIF** function and then taking the reciprocal of each count. Finally, it sums all the reciprocals to get the count of unique values.

**Can the formula for counting distinct values be used for non-numeric values?**

Yes, the formula for counting distinct values can be used for non-numeric values such as text or dates. It will treat each unique text or date value as a distinct value in the range.

**Can the formula for counting distinct values be used for non-numeric values?**

Yes, the formula for counting distinct values can be used for non-numeric values such as text or dates. It will treat each unique text or date value as a distinct value in the range.