Function Problems

How to Use SUMPRODUCT to Count Cells by Multiple Criteria in Spreadsheets

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to use the SUMPRODUCT function to count nonblank cells in one column while excluding specific values (such as RED or PURPLE) in another column, without accidentally counting the header row.

Product: Spreadsheets
Device & OS: not provided
Scenario: Counting rows based on multiple complex conditions, such as verifying non-blank cells and excluding specific text values simultaneously.
Observed behavior: Using full-column references in SUMPRODUCT accidentally evaluates the header row, resulting in a count that is one higher than the actual data.

Before you start

Ensure your dataset does not have merged cells within the calculation ranges, and verify the exact row numbers where your data starts and ends to avoid header row miscalculations.

Solution 1Recommended

Use Defined Data Ranges to Exclude Headers

Specify exact row numbers instead of full-column references to prevent the formula from counting the header row.

By defining specific start and end rows (e.g., I2:I1000), you ensure the SUMPRODUCT function only evaluates actual data. In SUMPRODUCT, TRUE is treated as 1 and FALSE as 0. Multiplying these conditions creates AND logic.

Select the result cell

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

Enter the SUMPRODUCT formula

Type the formula =SUMPRODUCT((I2:I1000<>"")*(J2:J1000<>"RED")*(J2:J1000<>"PURPLE")), adjusting the row numbers to match your dataset.

Calculate the result

Press Enter. A row contributes 1 to the total only when column I is nonblank and column J is neither RED nor PURPLE.

Solution 2

Adjust Full-Column References Manually

If you prefer using full-column references, manually adjust the final calculation to account for the header row.

Solution 3

Use XMATCH for Lookup-Based Exclusions

When you have a long list of criteria to exclude, referencing a separate range is cleaner than hardcoding each condition.

Advanced Spreadsheet Functions

Master SUMPRODUCT and Multiple Criteria Counting in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT and XMATCH, making complex data analysis straightforward. Use WPS Office to manage your datasets, apply conditional counting, and generate accurate reports seamlessly.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data.
2. Access the function library: Click on the Formulas tab in the top ribbon and select Insert Function.
3. Set up SUMPRODUCT: Search for SUMPRODUCT and input your array criteria into the dialog box.
4. Apply and calculate: Click OK or press Enter to instantly calculate your multi-condition data.

Fully compatible with Microsoft Excel formulas and functions.Lightweight software with fast calculation speeds for large datasets.Built-in function wizard to help you write complex formulas without errors.Free and cross-platform, allowing you to edit spreadsheets on any device.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMPRODUCT formula return an error when using multiple criteria?

This usually happens if the array ranges in your formula are not the exact same size. Ensure that all ranges, such as I2:I1000 and J2:J1000, have the exact same number of rows.

Can I use COUNTIFS instead of SUMPRODUCT for multiple criteria?

Yes, COUNTIFS can handle multiple criteria and is often faster for simple conditions. However, SUMPRODUCT is more versatile for complex array logic, such as performing mathematical operations on the arrays before counting them or working with functions like XMATCH.

What does multiplying criteria with an asterisk (*) do in SUMPRODUCT?

The asterisk acts as an AND operator. It forces the TRUE or FALSE results of each condition to be converted into 1s and 0s. A row is only counted (results in 1) if all multiplied conditions are TRUE.

Does SUMPRODUCT count hidden rows?

Yes, SUMPRODUCT evaluates all cells in the specified range, regardless of whether the rows are hidden or filtered. To exclude hidden rows, you need to combine SUMPRODUCT with the SUBTOTAL function.

Excel & Data Problems