How to Ignore Blanks and Count Duplicates in Excel (3 Ways)

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.

Dataset-Count Duplicates in Excel Ignoring Blanks

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


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.

sumproduct sumif 1-Count Duplicates in Excel Ignoring Blanks

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

sumproduct sumif result

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 TRUEs 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.

countif -Count Duplicates in Excel Ignoring Blanks

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

Fill Handle

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)

Formula insertion

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

Result-Count Duplicates in Excel Ignoring Blanks

 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:


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.

vba dataset-Count Duplicates in Excel Ignoring Blanks

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.

Module insertion

 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

macro

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.

vba macro-Count Duplicates in Excel Ignoring Blanks

 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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo