Formula Errors

How to Count Rows When One Column is Blank Using SUMPRODUCT in Excel

Amos Gikunda Oct 21, 2026 868 views

Question details

The user needs a single formula to count the number of rows where one specific column is blank and another column contains a specific value.

How to Count Rows When One Column is Blank Using SUMPRODUCT

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Data analysis requiring conditional counting across multiple columns where one condition is an empty cell.
Observed behavior: A single formula evaluates row by row, checking both the blank condition in one column and the specific text condition in another, returning the total count of matching rows.

Before you start

Ensure your data ranges for both columns are exactly the same size. Mismatched array sizes will result in a #VALUE! error when using the SUMPRODUCT function.

Solution 1Recommended

Use the SUMPRODUCT Function for Multi-Condition Counting

Apply the SUMPRODUCT formula to evaluate arrays and count rows meeting multiple criteria, including blank cells.

The SUMPRODUCT function is highly versatile for evaluating multiple conditions without needing to enter it as an array formula. By multiplying the two condition arrays, TRUE and FALSE values are converted into 1s and 0s, allowing the function to sum the matching rows.

Select the output cell

Click on the cell where you want the final count result to appear.

Enter the SUMPRODUCT formula

Type the formula =SUMPRODUCT((E5:E20="")*(F5:F20="X")) into the formula bar.

Adjust the cell ranges

Change E5:E20 to the actual range of your first column (the one you want to check for blanks) and F5:F20 to the corresponding range in your second column.

Calculate the result

Press Enter to calculate. The formula will return the total number of rows where column E is empty and column F contains the letter X.

Use the SUMPRODUCT Function for Multi-Condition Counting

Understanding the syntax: The expression (E5:E20="") checks for blanks, returning TRUE or FALSE. Multiplying it by (F5:F20="X") converts these boolean values to 1s and 0s, which SUMPRODUCT then adds together.

Solution 2

Alternative: Use COUNTIFS for Conditional Counting

Use the COUNTIFS function as a more straightforward alternative for counting rows with multiple criteria.

Advanced Formulas in WPS Office

Count and Analyze Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports SUMPRODUCT, COUNTIFS, and over 400 other advanced Excel functions. Handle complex data calculations effortlessly with a familiar interface.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open your existing Excel file (.xlsx or .xls).
2. Input the formula: Select a blank cell and enter =SUMPRODUCT((E5:E20="")*(F5:F20="X")).
3. Get instant results: Press Enter to instantly count the rows matching your blank and specific text criteria.

100% compatible with Microsoft Excel formulas like SUMPRODUCT and COUNTIFS.Free to use with a lightweight installation package.Built-in data analysis tools for quick insights.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMPRODUCT formula return a #VALUE! error?

This usually happens if the array ranges in your formula are not of equal size. Ensure that E5:E20 and F5:F20 have the exact same number of rows.

Can I use SUMPRODUCT to count cells that are NOT blank?

Yes, you can change the blank criteria in the formula from ="" to <>"". For example, use =SUMPRODUCT((E5:E20<>"")*(F5:F20="X")) to count rows where column E is not empty.

What is the difference between SUMPRODUCT and COUNTIFS for this task?

COUNTIFS is generally faster and designed specifically for counting with multiple criteria. However, SUMPRODUCT is more flexible and can handle arrays or complex calculations within the conditions that COUNTIFS cannot process natively.

Excel & Data Problems