Function Problems

How to Use SUMIFS Syntax for Formula-Based Criteria (Exclude Blanks)

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to know the correct syntax to sum data using criteria based on formulas, specifically looking to exclude empty cells or formula-generated blank strings using a function like SUMIFS.

Product: Spreadsheets
Device & OS: not provided
Scenario: Calculating a conditional sum where the criteria range includes blank cells or empty text strings that need to be excluded from the calculation.
Observed behavior: The user is unsure of the exact syntax required to handle complex conditions, such as distinguishing between completely empty cells and formula-generated empty strings.

Before you start

Before applying the formula, check your dataset to identify whether the cells you want to exclude are completely empty (blank) or if they contain formulas that return empty text strings, as the required syntax differs for each.

Solution 1Recommended

Exclude Completely Blank Cells Using the Not-Equal Operator

Use this standard syntax when you want to sum values while ignoring cells that are entirely empty (contain no data or formulas).

The standard method for excluding empty cells in a SUMIF or SUMIFS function is to use the not-equal operator. This operator ensures that only cells containing some form of data are evaluated.

Select the destination cell

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

Enter the SUMIFS formula

Type `=SUMIFS(Sum_Range, Criteria_Range, "<>")`. Replace 'Sum_Range' with the numbers you want to add, and 'Criteria_Range' with the cells you are checking for blanks.

Execute the calculation

Press Enter to apply the formula. The result will now sum the values where the criteria range is strictly not empty.

Solution 2

Exclude Formula-Generated Empty Strings

Use this approach if your criteria range contains formulas that return empty strings (""), which the standard operator might fail to exclude.

Easily Calculate Conditional Sums with WPS Spreadsheet

WPS Spreadsheet offers full support for advanced functions like SUMIFS, allowing you to easily handle complex data calculations and formula-based criteria.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data.
2. Access the Formula function: Navigate to the 'Formulas' tab on the top ribbon and click 'Insert Function'.
3. Set up SUMIFS: Search for 'SUMIFS', select it, and use the dialog box to input your sum range and criteria (such as "<>").
4. Apply and calculate: Click 'OK' to instantly calculate your conditional sum with the correct syntax applied.

Fully compatible with Microsoft Excel formulas like SUMIFS, SUMIF, and others.Intuitive formula builder to help you select ranges and criteria without syntax errors.Free, lightweight, and fast data processing.

microsoft office alternative - wps office

Frequently Asked Questions

How do I use a cell reference as a criterion in SUMIFS?

To use a cell reference alongside a logical operator, you must use the ampersand (&) to concatenate them. For example, to sum values greater than the value in cell C1, use the syntax: `=SUMIFS(B2:B10, A2:A10, ">"&C1)`.

Why is my SUMIFS formula returning 0 when excluding empty cells?

This usually happens if the criteria range and the sum range are mismatched in size, or if the criteria syntax is incorrectly formatted. Ensure you are using double quotes around the operator, such as `"<>"`, and that both ranges contain the exact same number of rows.

Can I use other functions directly inside the SUMIFS criteria?

Yes, you can combine functions within the criteria using concatenation. For instance, to sum values based on today's date, you can input `=SUMIFS(B2:B10, A2:A10, ">"&TODAY())`.

Excel & Data Problems