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.
How to Ignore Blanks and Count Duplicates in Excel: 3 Ways
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 Use COUNTIF Formula to Find Duplicates
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)>1The 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 Duplicate Values Only Once in Excel
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: How to Count Repeated Words in Excel
Download Excel Workbook
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.
