To sum only positive values in Excel, use SUMIF with the criterion ">0". If the numbers are in A2:A8, enter =SUMIF(A2:A8,">0"); Excel adds values greater than zero and ignores negative numbers and zeros.
How SUMIF includes positive values
SUMIF checks each cell against a condition, then adds the values that meet it. In this case, the condition is “greater than zero.” The zero matters: a value of 0 is not positive, so it is excluded along with negative values.
In Microsoft Excel for Microsoft 365 on Windows, enter the formula in an empty cell and press Enter. Excel calculates the total from the selected range. The formula can go above, below, or on another worksheet from the data, provided the range reference points to the right cells.
The general syntax is SUMIF(range,criteria,[sum_range]). The first argument is the cells Excel evaluates, the second is the condition, and the optional third argument is the cells Excel adds. When you want to test and sum the same cells, you can leave out the third argument, as in the opening formula. The quotation marks around ">0" are required because the comparison is supplied as a text criterion.
A worked example with positive, negative, and zero values
Suppose A2:A8 contains 120, -35, 0, 48, -12, 7, and 0. The positive entries are 120, 48, and 7. Their total is 175; the negative values and zeros do not contribute.
With those values in A2:A8, use this formula in another cell; it returns 175:
=SUMIF(A2:A8,">0")
This result is a sum, not a count. To count positive entries, use a counting function rather than interpreting the SUMIF total as the number of matches. The distinction matters when the qualifying values vary: a total of 175 could come from three values, one value, or many values.
If you want the formula to remain tied to the same range when you copy it elsewhere, use absolute references. With the same values in A2:A8, this version also returns 175:
=SUMIF($A$2:$A$8,">0")
The dollar signs lock the row and column references. They are useful when copying formulas, but are not necessary for a single total.
Summing a separate range based on positive values
Sometimes the cells that decide whether a row qualifies are not the cells you want to add. For example, a worksheet might record a signed adjustment in one column and an amount to total in another. In that case, the first range is the test range and the optional third argument is the sum range.
Assume A2:A6 holds adjustments of 5, -2, 0, 3, and -1, while B2:B6 holds corresponding amounts of 40, 25, 10, 60, and 15. The positive adjustments occur in rows 2 and 5? In this layout, they occur in rows 2 and 5 only if the values are 5 and 3; those are actually rows 2 and 5 for the stated range A2:A6: 5 is in row 2 and 3 is in row 5. Their corresponding amounts are 40 and 60, totaling 100.
With adjustments in A2:A6 and amounts in B2:B6, this formula returns 100:
=SUMIF(A2:A6,">0",B2:B6)
Keep the test range and sum range aligned: each cell in one range should correspond to the same row in the other. If the ranges have different sizes or start on different rows, the result may not represent the records you intended. For a straightforward same-column total, omit the sum range to reduce the chance of misalignment.
Adding another condition with SUMIFS
You can combine the positive-value test with another condition, such as a region, department, or product. SUMIFS is designed for multiple criteria. Its argument order differs from SUMIF: it starts with the sum range, followed by pairs of criteria ranges and criteria.
Suppose A2:A6 contains regions and B2:B6 contains values: North 100, South -20, North -15, South 80, and North 40. To add only positive values for North, the qualifying amounts are 100 and 40, so the result is 140.
With regions in A2:A6 and amounts in B2:B6, this formula returns 140:
=SUMIFS(B2:B6,B2:B6,">0",A2:A6,"North")
Here, the first pair of criteria arguments filters the amounts to values above zero, and the second pair keeps rows whose region is North. In a larger sheet, you could refer to a cell containing the region name instead of typing North into the formula. That makes it easier to change the requested region without editing the formula itself.
Choosing between SUMIF and other approaches
For one positive-only total, SUMIF is usually the clearest option. It expresses the rule directly and is easy to audit later. Use SUMIFS when the total must meet two or more conditions, such as positive amounts for a particular date range and category.
Manual filtering can help you inspect the qualifying rows, but a filter alone does not turn a normal sum into a positive-only sum. A formula such as SUM over the original range still includes negative numbers, and filtering rows does not automatically make every ordinary total formula visible-rows-only. If the requirement is a repeatable calculated result, put the positive condition in the formula.
Some users try to replace negative numbers with zero before summing. That changes the underlying data and can remove information needed for later analysis. A conditional sum leaves the original entries intact and calculates a separate result, which is generally safer for a report or reconciliation.
Common problems and how to fix them
- The formula returns zero. Check that the values are actual numbers and that the range covers the intended cells. Numbers imported from a text file may be stored as text, in which case they might not behave like numeric values. Use Excel’s conversion tools or correct the source data, then recalculate.
- Some values seem to be missed. Check for leading or trailing spaces in imported data, text-formatted numbers, or a range that stops before the last row. A value that looks like 12 may be text rather than the number 12.
- The total is too high. Confirm that you have not included duplicate records, and check whether the formula’s range includes a previous subtotal or a total cell. Expanding a range to an entire column can be convenient, but it can also include unintended values placed elsewhere in that column.
- The formula shows an error. Verify that parentheses and quotation marks are balanced and that each range reference is valid. In the formulas shown here, the criterion must be written as
">0". A missing quotation mark or an incorrectly typed range can prevent Excel from calculating the result. - Your Excel expects semicolons. The examples use commas, which are the standard argument separators in many installations. Depending on regional settings, Excel may require semicolons between function arguments. If Excel rejects a formula with commas, use the separator configured for your installation while keeping the criterion text intact.
Also remember that a positive-only sum includes every matching cell in the specified range, whether or not its row is currently hidden. Sorting or applying a table filter does not change the meaning of the SUMIF condition. If you need a total of only visible positive rows, use a method built specifically for visible rows or add a helper column and verify that the chosen calculation respects the filter. Do not assume that hiding rows changes the result.
Use the formula in a growing list
For a short, fixed list, a range such as A2:A8 is simple and transparent. If new records are added regularly, remember that a formula referencing A2:A8 will not automatically include entries below row 8. You can update the range, use a larger planned range, or format the records as an Excel table and use its column references in formulas.
Whichever approach you choose, keep the data column consistent: put one value per row, avoid inserting labels or subtotals among the records, and make sure the total cell is outside the range being summed. These habits make positive-only totals easier to check and reduce accidental double-counting.
Frequently Asked Questions
Does SUMIF exclude zero?
Yes. The criterion ">0" means strictly greater than zero, so zero does not qualify. Negative numbers do not qualify either.
How do I sum positive numbers in one column based on another column?
Use SUMIF with the column containing the condition as the first range and the column to add as the sum range. The two ranges must line up row by row. If you have several conditions, use SUMIFS.
Can I sum positive values while ignoring negative numbers without changing the data?
Yes. A positive-only SUMIF calculates a separate total and leaves the original cells unchanged. This is preferable to replacing negatives with zeros when you still need the original figures.
Does SUMIF count positive values or add them?
It adds the values that meet the condition. If you need to know how many cells are positive, use COUNTIF with the same greater-than-zero criterion.
Will SUMIF update when I add more rows?
It updates for changes within its referenced range, but a fixed reference such as A2:A8 does not automatically extend to row 9. Expand the range or use an Excel table if the list is expected to grow.
Does a filter make SUMIF total visible positive rows only?
No. SUMIF evaluates the cells in its referenced range, including rows hidden by a filter. A visible-rows-only total needs a separate approach designed to account for hidden rows.
Related
- Excel Max Rows per Sheet: Limits and Workarounds
- Excel Max Value in a Column: Find the Largest Number
- Excel Maximum Rows: Limits and What to Do
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Plus Weeks: Formulas, Dates, and ExamplesTo add weeks to a date in Excel, multiply the number of weeks by 7 and add the…
- LEFT and LEN Excel Formula: How to Use Them TogetherTo remove the last character from text in Excel, combine LEFT and LEN like this: =LEFT(A2,LEN(A2)-1). With the…
- XLOOKUP Example With SUM in ExcelWith product names in A2:A5, monthly values in B2:D5, and the product to find in E2, use =SUM(XLOOKUP(E2,A2:A5,B2:D5)).…
- Match Excel Formula for Two Columns: Compare ValuesTo compare two cells on the same row in Excel, enter =IF(A2=B2,"Match","Different") in C2 and fill it down.…
- SUMIF Greater Than in Excel: Criteria and ExamplesTo use SUMIF in Excel for values greater than a number, put the comparison in quotation marks as…
- Excel OFFSET Function: How to Use It in Formulas and RangesIn Excel for Microsoft 365 on Windows, OFFSET returns a reference moved a chosen number of rows and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.