Function Problems

How to Apply Row-by-Row Criteria in SUMIF or SUMIFS

Huda Qurayshi Oct 8, 2026 872 views

Question details

The user needs to sum values based on a condition evaluated row-by-row, such as comparing cells across different ranges or using FIND/SEARCH functions.

How to Apply Row-by-Row Criteria Using SUMIF Alternatives

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: Attempting to evaluate a dynamic expression like FIND or SEARCH for every cell within a criteria range to sum corresponding values.
Observed behavior: Native SUMIF and SUMIFS functions cannot directly evaluate dynamic array expressions or nested FIND/SEARCH functions row-by-row, requiring an alternative approach.

Before you start

Ensure that the criteria range and the sum range in your dataset are of the exact same size to avoid #VALUE! errors during array calculations.

Solution 1Recommended

Use the SUMPRODUCT Function for Dynamic Array Evaluation

The most efficient way to perform row-by-row criteria checks without altering your data structure is by using SUMPRODUCT.

Because SUMIF and SUMIFS do not support processing array functions like SEARCH or FIND directly within their criteria arguments, SUMPRODUCT is the standard alternative. It handles array operations natively.

Select the target cell

Click on the cell where you want the final calculated sum to be displayed.

Enter the SUMPRODUCT formula

Type the formula =SUMPRODUCT(--ISNUMBER(SEARCH(H3,C3:C86)),D3:D86) to perform a non-case-sensitive search. This checks if the value in H3 is found within each cell of C3:C86, summing the corresponding values in D3:D86.

Adjust for case-sensitivity (Optional)

If you need a strictly case-sensitive match, replace the SEARCH function with FIND. The formula becomes =SUMPRODUCT(--ISNUMBER(FIND(H3,C3:C86)),D3:D86).

Execute the formula

Press the Enter key on your keyboard to calculate the result. The double hyphen (--) will convert the TRUE/FALSE results into 1s and 0s so they can be multiplied.

Use the SUMPRODUCT Function for Dynamic Array Evaluation

Solution 2

Create a Helper Column for Complex Data with Errors or Blanks

If your data contains blank cells or potential calculation errors, using a helper column simplifies the process and prevents the primary formula from breaking.

Solve complex data tasks effortlessly

Master Advanced Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT, SEARCH, and FIND, allowing you to seamlessly perform row-by-row conditional summing without complex programming.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing the data you need to analyze.
2. Select a calculation cell: Click on the empty cell where you wish to display the summed results.
3. Access the formula bar: Click into the formula bar at the top or simply begin typing '=' to start your function.
4. Input the array formula: Type your SUMPRODUCT and ISNUMBER combination, select your data ranges, and press Enter to instantly evaluate the row-by-row criteria.

Fully compatible with Microsoft Excel formulas and functionsProcesses advanced array formulas like SUMPRODUCT flawlesslyLightweight application with high performance for large datasetsFree to use for everyday data analysis and tracking

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't SUMIF accept functions like SEARCH directly in its criteria?

SUMIF and SUMIFS are designed to evaluate simple criteria elements like text strings, numbers, or basic logical operators (>, <) against a range. They cannot natively process nested functions like SEARCH that return an array of results. To process an array, SUMPRODUCT must be used.

What does the double hyphen (--) do in the SUMPRODUCT formula?

The double hyphen, known as a double unary operator, forces Excel and WPS Spreadsheet to convert boolean values (TRUE or FALSE) returned by functions like ISNUMBER into numerical values (1 or 0). This allows SUMPRODUCT to multiply these values against the sum range mathematically.

Can I use wildcards with SUMIF instead of using SUMPRODUCT?

Yes, if your criteria involves a fixed text pattern, you can use asterisks (*) as wildcards directly in SUMIF (e.g., =SUMIF(C3:C86, "*apple*", D3:D86)). However, if you need to dynamically compare two different ranges row-by-row, or reference another cell as a dynamic substring, you must use SUMPRODUCT.

Excel & Data Problems