Function Problems

How to Use SUMIFS in Excel to Sum Rows with Positive Numbers

Emma Brown Oct 9, 2026 868 views

Question details

The user wants to conditionally sum values from one column based on whether the corresponding cells in another column contain positive numeric values.

How to Use SUMIFS in Excel to Sum Rows with Positive Numbers

Product: Excel
Device & OS: not provided
Scenario: Calculating a conditional sum of a data range where adjacent or related cells contain numerical values greater than zero.
Observed behavior: A formula is required to filter out non-positive entries (zero, negative, or blank) in a criteria column and dynamically sum the corresponding values in a sum range.

Before you start

Ensure your dataset is organized in clean columns without merged cells, and verify that the column you are evaluating contains actual numeric values rather than text formatted to look like numbers.

Solution 1Recommended

Using the SUMIFS Function with a >0 Criterion

The most straightforward method to sum rows based on positive numbers is applying the SUMIFS function with a greater-than-zero criterion applied to the evaluation range.

The SUMIFS function allows for multiple conditions, making it flexible for this scenario. By setting the criterion to ">0", Excel will only add the values from your sum range if the corresponding cell in the criteria range is a positive number.

Select a destination cell

Click on an empty cell where you want the final sum result to be displayed.

Start the SUMIFS formula

Type =SUMIFS( to begin the function.

Select the sum range

Highlight the range of values you actually want to add up (for example, F2:F892), then type a comma.

Select the criteria range

Highlight the range of cells you need to evaluate for positive numbers (for example, G2:G892), and type another comma.

Enter the condition

Type ">0") including the quotation marks to set the condition for positive numbers.

Calculate the result

Press the Enter key. Your final formula should look like =SUMIFS(F2:F892, G2:G892, ">0").

Using the SUMIFS Function with a >0 Criterion

Important Syntax Rule: In Excel, logical operators like > (greater than) must be enclosed in double quotation marks when used inside the criteria argument of a SUMIF or SUMIFS function.

Smart Formula Calculation

Easily Calculate Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports the SUMIFS function and provides an intuitive visual Formula Builder to help you accurately configure complex calculations without memorizing syntax.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the file containing your data.
2. Launch the Formula Builder: Navigate to the Formulas tab on the top ribbon and click on Insert Function.
3. Find the SUMIFS function: Type SUMIFS in the search box, select it from the list, and click OK to open the argument dialog.
4. Fill out the arguments: Use your mouse to select your Sum_range and Criteria_range1, then simply type >0 in the Criteria1 box.
5. Apply and calculate: Click OK to automatically insert the correctly formatted formula into your cell and view the result.

Fully compatible with Microsoft Excel formulas including SUMIFS, VLOOKUP, and conditional logic.Intuitive Insert Function dialog box that guides you step-by-step through formula creation.High-speed processing capable of smoothly handling spreadsheets with thousands of rows.

microsoft office alternative - wps office

Frequently Asked Questions

How do I sum rows where cells contain any number, including negative numbers and zero?

If you want to sum based on the presence of any numerical value (not just >0), you can use a combination of SUMPRODUCT and ISNUMBER. The formula =SUMPRODUCT(--ISNUMBER(G2:G892), F2:F892) will evaluate if the cells in column G are numbers and sum the corresponding values in column F.

Why is my SUMIFS formula returning a 0 or a #VALUE! error?

A #VALUE! error usually occurs if your sum_range and criteria_range are not the exact same size (e.g., F2:F800 and G2:G892). If the result is 0 unexpectedly, verify that the numbers in your criteria range are formatted as numerical values and not as text.

Can I use the SUMIF function instead of SUMIFS for this task?

Yes, since there is only one condition being tested, you can use SUMIF. The syntax for SUMIF reverses the order of the arguments compared to SUMIFS. The equivalent formula would be =SUMIF(G2:G892, ">0", F2:F892).

Excel & Data Problems