Count Distinct Values While Ignoring Empty Cells in Excel

When an Excel list contains duplicate entries and blank cells, counting the number of different values can be difficult. Functions such as COUNTA count every nonempty cell, including duplicates, while manually removing duplicate records may alter the original data.

The Count distinct values ignoring empty cells formula in Formula Helper Plus provides a more convenient solution. Simply select the data range, and Formula Helper Plus automatically generates the required formula to count each nonempty value only once—without requiring you to write or memorize a complex formula.

• Advantages of Count Distinct Values Ignoring Empty Cells
• How to Count Distinct Values While Ignoring Empty Cells in Excel
• Tips for Counting Distinct Values
• FAQ - Frequently Asked Questions
• Conclusion

Advantages of Count Distinct Values Ignoring Empty Cells

This formula is useful when you need to determine how many different customers, products, employees, departments, or other entries appear in a range containing both duplicates and blank cells.

Its main advantages include:

How to Count Distinct Values While Ignoring Empty Cells in Excel

In this example, a customer order list contains 50 order records. Some customers placed multiple orders, while some records have no customer assigned. We’ll count the number of distinct customers in the Customer column without including the empty cells.

To count the distinct customers while ignoring the empty cells without manually writing a formula, follow these steps.

Step 1: Select the output cell

First, select the cell where you want the distinct-value count to appear. The generated formula will be inserted directly into this cell.

Step 2: Open Formula Helper Plus

Select Kutools > Formula Helper > Formula Helper Plus.

Step 3: Find the formula and configure the argument

Formula Helper Plus organizes formulas into categories and also provides a search box, making it easier to locate the required formula without knowing its syntax.

In the dialog box:

1. Shift to the Local tab.
2. Select Count in the Category list.
3. Select Count distinct values ignoring empty cells from the Formula list.
   Alternatively, enter a keyword such as distinct in the search box to locate the formula more quickly.
4. Click inside the Range box, and then select the cells you want to check for distinct nonempty values. Exclude the column heading.
5. Click OK.

   

Result

Formula Helper Plus inserts the formula into the output cell and immediately returns the number of distinct nonempty customers.

In this example, the result is 14. Customers with multiple orders are counted only once, while empty cells are excluded from the result.

Tips for Counting Distinct Values

The accuracy of the result depends on the data contained in the selected range. Entries that look identical or blank may contain subtle differences that cause Excel to treat them as separate values.

Follow these practices when counting distinct values:

FAQ – Frequently Asked Questions

The following questions address common issues when counting distinct values while excluding empty cells.

Conclusion

The Count distinct values ignoring empty cells formula makes it easy to count each nonempty value only once in a range containing duplicates and blanks. With Formula Helper Plus, you only need to select the source range to generate the formula and obtain a dynamic result—without writing or memorizing complex formula syntax.