Function Problems

How to Use SUMIFS with Multiple OR Criteria in Excel

Maira Mehtab Sep 28, 2026 870 views

Question details

The user needs to sum rows in a dataset based on multiple criteria, specifically where one column must match a specific value and another column must contain either of two specified text strings.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Calculating conditional sums where one of the conditions requires an 'OR' logic check within a single column.
Observed behavior: The standard SUMIFS formula returns zero because it attempts to process multiple criteria for the same column as an 'AND' condition, which is impossible to satisfy if looking for two different strings simultaneously.

Before you start

Ensure your source data does not contain hidden trailing spaces and that the column you are summing contains numeric values, not numbers stored as text.

Solution 1Recommended

Use SUM and SUMIFS with an Array Constant

Wrap your SUMIFS function inside a SUM function and use an array constant to evaluate multiple 'OR' conditions in a single column.

By default, applying multiple criteria to the same column in a SUMIFS formula creates an 'AND' condition. By using an array constant, SUMIFS evaluates each item and returns an array of results. Wrapping this in a SUM function aggregates these results into a single total.

Select the target cell

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

Enter the SUM function

Type =SUM( to begin the formula.

Nest the SUMIFS function

Inside the SUM function, type your SUMIFS formula using curly brackets for the criteria array: =SUM(SUMIFS('Twilio SMS - Vendor Report'!$K:$K, 'Twilio SMS - Vendor Report'!$D:$D, D2, 'Twilio SMS - Vendor Report'!$I:$I, {"*Authy*","*Verify*"}))

Calculate the result

Press Enter to calculate the formula. The cell will now sum the rows matching either 'Authy' or 'Verify' in the target column.

Wildcard Usage: The asterisks (*) around the text criteria act as wildcards, meaning the cell only needs to contain the word anywhere in the text string, rather than matching it exactly.

WPS Spreadsheet

Master Advanced Formulas with WPS Spreadsheet

Easily handle complex data analysis, including SUMIFS with multiple OR criteria, using WPS Spreadsheet. It offers full compatibility with standard spreadsheet functions and a user-friendly interface for all your data processing needs.

1. Open WPS Spreadsheet: Launch WPS Office and open your data workbook.
2. Use the Formula Tab: Navigate to the Formulas tab to access the Insert Function tool if you need help building complex nested functions.
3. Apply the Array Formula: Enter the =SUM(SUMIFS(...)) formula exactly as you would in Excel to instantly calculate your 'OR' criteria sums.

100% compatibility with Microsoft Excel formulas and formats (.xlsx).Intuitive formula builder and syntax highlighting to prevent errors.Lightweight software that processes large datasets quickly without lagging.Free to use for everyday spreadsheet and data analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my standard SUMIFS formula return zero for OR criteria?

A standard SUMIFS formula uses 'AND' logic for all criteria. If you specify that a single cell must equal 'Authy' and also equal 'Verify', it is logically impossible for a single cell to be exactly both at the same time, resulting in a sum of zero.

Can I use cell references instead of hardcoding the text in the array constant?

Yes, but you cannot use cell references inside a standard array constant (curly brackets). Instead, you must use a function like SUMPRODUCT or enter the formula as an array formula (Ctrl+Shift+Enter) referencing a range of cells.

What if my text criteria do not need wildcards?

If you need an exact match rather than a partial match, simply remove the asterisks from the array constant in your formula. For example, use {"Authy","Verify"} instead of {"*Authy*","*Verify*"}.

Excel & Data Problems