How to Ignore Blanks and Count Duplicates in Excel – 3 Methods

The sample dataset showcases Order Date, Category, Product, and Sold Quantity. There are blank cells in the dataset. To count duplicates ignoring blanks:

Method 1 – Using the SUMPRODUCT and COUNTIF Functions

Step 1:

• Enter the following formula in a blank cell (here, G5).

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

The SUMPRODUCT function adds occurrences greater than 1 by taking  –(COUNTIF(B5:E16,B5:E16)>1 as an array.

Step 2:

• Press ENTER.

The number of duplicates is displayed in G5.

Read More: How to Use COUNTIF Formula to Find Duplicates

Method 2 – Using the COUNTIF Function to Count Duplicates in Excel Ignoring Blanks

Step 1:

• Insert a helper column, Status, and use the formula below:

The COUNTIF function returns TRUE or FALSE for B5:B16 (the range named Product) and criteria D5 (each cell) depending on the existence of duplicates.

Step 2:

• Press ENTER
• Drag down the Fill Handle to display TRUE or FALSE.

Step 3:

• Enter the formula in a blank cell (here, H5).

Step 4:

• Press ENTER.

This is the output.

Related Content: How to Count Duplicate Values Only Once in Excel

Method 3 – Using a VBA Macro to Count Duplicates Ignoring Blanks

Consider the dataset below:

Step 1:

• Press ALT+F11 to open the Microsoft Visual Basic window.
• Select Insert and choose Module.

 Step 2:

Copy 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 SUMPRODUCT and COUNTIF formula used the previous Method.

Step 3:

• Press F5 to run the macro.
• Go back to the worksheet.

You’ll see the count in G5:

 Read More: How to Count Repeated Words in Excel

Download Excel Workbook

Related Articles

• How to Count Duplicates with Pivot Table in Excel
• VBA to Count Duplicates in Range in Excel
• Excel VBA to Count Duplicates in a Column

<< Go Back to Count Duplicates in Excel | Learn Excel