It’s common that blanks exist in Excel datasets. There are incidences, where we want to count duplicates in Excel as well as ignore blanks. Counting duplicates is an easy hack to comprehend a dataset.

Let’s say, we have a dataset with *Order Date*, *Category*, *Product*, and *Sold Quantity*. There are also no sales on certain dates. So, blank cells are present in the dataset. We want to count duplicate entries within the dataset ignoring blanks.

** **In this article, we demonstrate ways to count duplicates in Excel ignoring blanks.

**Table of Contents**hide

**Download Excel Workbook**

**3 Easy Ways to Ignore** Blanks When to Count** Duplicates in Excel**

**Method 1: Using SUMPRODUCT and COUNTIF Functions**

Normally counting non-blank cells in Excel requires simple functions. But whenever we put criteria in counting combined functions or a helper column is needed. In this case, we use a formula generated by combined functions to count duplicates ignoring blanks.

**Step 1:** Paste the following formula in any blank cells (i.e., **G5**).

`=SUMPRODUCT(--(COUNTIF(B5:E16,B5:E16)>1))`

**🔁 **The **COUNTIF** function takes **B5:E16** as range and criteria to pass the count number of each entry in numbers (i.e., **1** for unique and **2 **or greater in case of duplicates).

**🔁 **Then, the **SUMPRODUCT** function adds those occurrences greater than **1** by taking the **–(COUNTIF(B5:E16,B5:E16)>1** portion as an **array**.

**Step 2: **To apply the formula hit **ENTER**. The number of duplicates appears in cell **G5**.

In case you have any double regarding the result, just inspect the dataset. We’ve numbered the duplicates and the number of them exactly sums **21**.

**Read More:**** How to Count Duplicates Based on Multiple Criteria in Excel**

**Method 2: COUNTIF Function to Count Duplicates in Excel Ignoring Blanks**

In the previous method, we mentioned inserting a helper column to assist in counting duplicates ignoring blanks. We first display duplicate status for entries in a column. For that reason, we use the **COUNTIF** function to display **TRUE** or **FALSE** respectively, the entries being duplicate or unique. Afterward, another **COUNTIF** function counts the **TRUE**s and returns the count.

**Step 1:** Insert a helper column, *Status*, beside the range and use the below formula.

`=COUNTIF(Product,D5)>1`

The **COUNTIF** function returns **TRUE** or **FALSE** for **B5:B16** (i.e., range named **Product**) and criteria** D5** (i.e., each respective cell) depending on their duplicates value existence.

**Step 2: **Press **ENTER** and Drag the** Fill Handle** to display the **TRUE** or **FALSE** status as depicted in the following picture.

**Step 3: **After revealing the column **D** entries’ status as **TRUE** or** FALSE**, type the below formula in any blank cell (i.e., **H5**).

`=COUNTIF(Status,TRUE)`

**Step 4: **Use the **ENTER** key to display the duplicates’ count in the formula cell.

** **

** **For clarification, we’ve numbered duplicates of the* Product* column. And the formula counts the duplicates exactly **8**.

**Related Content: How to Count Duplicates in Two Columns in Excel (8 Methods)**

**Similar Readings:**

**Count Duplicates in Excel Pivot Table (2 Easy Ways)****How to Count Duplicate Rows in Excel (4 Methods)****Count Duplicates in Column in Excel (3 Ways)****How to Count Occurrences Per Day in Excel (4 Quick Ways)****Excel Count Number of Occurrences of Each Value in a Column**

**Method 3: VBA Macro to Count Duplicates Ignoring Blanks**

**VBA** macro codes are efficient tools to achieve a criteria-based outcome. In this case, we use a **VBA** macro induced with a formula to count duplicates ignoring blanks.

Already we have a dataset where blanks exist as the following depiction.

**Step 1:** In order to open **Microsoft Visual Basic** window press **ALT+F11** altogether. In the **Microsoft Visual Basic** window Select **Insert** (from the **Toolbar**) > Select **Module**.

** ****Step 2: **Paste the following macro in the **Module**.

```
Sub Count_Duplicates_IgnoringBlnk()
Range("G5").Formula = "=SUMPRODUCT(--(COUNTIF(B5:E16,B5:E16)>1))"
End Sub
```

The macro executes the formula (i.e., **SUMPRODUCT** and **COUNTIF**) we use the earlier **Method**. The formula declares all the arguments as it does in the previous **Method**.

**Step 3: **Use the** F5** key to run the macro. Then, return to the worksheet, you see the count in the **G5** cell as instructed in the macro.

** **

** **Now, comparing the resultant value to the earlier resultant value of **Method 1**, you can see the macro returns exactly the same result.

**Read More: Excel VBA to Count Duplicates in a Column (A Complete Analysis)**

**Conclusion**

In this article, we use the entwined **COUNTIF** and **SUMPRODUCT** functions as well as a helper column to count duplicates in Excel and these ignore blanks. These above-mentioned methods are very effective in counting duplicates and ignoring blanks. Hope you find these methods workable with your data. Comment, if you have further inquiries or have anything to add.