SUMIF Positive Numbers in Excel: Formula and Examples

Formulas & functions · Updated November 20, 2026

To sum positive numbers in Excel, use SUMIF with the criterion ">0". If the numbers are in B2:B10, enter =SUMIF(B2:B10,">0"); Excel adds values greater than zero and excludes zero, negative numbers, and text.

How SUMIF identifies positive numbers

SUMIF checks cells against a condition and adds the values that meet it. Its syntax is SUMIF(range, criteria, [sum_range]). The first argument is the range Excel checks, the second is the condition, and the optional third argument identifies what to add. When you leave out the third argument, Excel sums the cells in the range it checked.

In the positive-number criterion, the greater-than sign means “strictly greater than,” and zero is the boundary. Excel requires a condition written with an operator and a value to be enclosed in quotation marks, so use ">0", not >0 without quotes. The formula does not mean “greater than or equal to zero”: that would include zero, which is not positive.

The formula can go in any suitable cell outside the data range. If you put it inside the range being summed, the formula may include its own result and create a circular reference. You do not need to sort the data or create a separate list of positive values first.

A worked example with a single range

Suppose B2:B7 contains 12, -4, 0, 7, -2, and 3. With those numbers in B2:B7, enter =SUMIF(B2:B7,">0") in a cell outside that range. The result is 22: Excel adds 12, 7, and 3, while ignoring -4, 0, and -2.

This basic form is useful for sales adjustments, inventory changes, test results, or any column where the values to check are also the values to total. It returns one total for the full range. It does not change the original values or hide the rows that do not qualify.

For a quick check, you can also filter the column to show values greater than zero and compare their visible total with the formula. Filtering is optional; SUMIF evaluates the whole range whether rows are visible or hidden by a filter.

Sum a different column when a number is positive

Sometimes the positive number is a condition, not the amount you want to add. For example, column A might contain units sold and column B might contain revenue. To total revenue only for rows where units sold are positive, use A as the criteria range and B as the sum range.

Assume A2:A7 contains 5, -2, 0, 3, -1, and 8, while B2:B7 contains 100, 40, 25, 60, 80, and 30. With those layouts, enter =SUMIF(A2:A7,">0",B2:B7). The result is 190: it adds 100, 60, and 30, the amounts on rows where the corresponding A value is above zero.

Keep the criteria range and sum range aligned row by row and, as a good practice, make them the same size. If the ranges start on different rows, or one is shifted relative to the other, Excel can associate a qualifying condition with the wrong amount. Descriptive headers are fine, but exclude headers from both ranges.

Enter the formula and make it reusable

In Excel for Microsoft 365 on Windows, select the cell where the total should appear, type the formula, and press Enter. The same formula syntax works in Excel for Mac and Excel for the web; the ribbon layout can differ, but no special command is needed to calculate a SUMIF result.

For a formula you plan to copy, fixed range references can prevent the checked cells from shifting. Suppose B2:B100 is the data, and you want to copy the formula to other cells. With the values in B2:B100, enter =SUMIF($B$2:$B$100,">0"). The dollar signs lock both ends of the range when you copy or fill the formula.

You can make the boundary adjustable by putting it in a cell. If B2:B100 contains the numbers and D1 contains the threshold zero, enter =SUMIF(B2:B100,">"&D1). The ampersand joins the greater-than operator to the value in D1. If D1 changes to 10, the formula totals numbers greater than 10; if it is zero, it totals positive values. This version also uses strictly greater than, so a value equal to the threshold is not included.

Excel normally recalculates the result when cells in the referenced ranges change. If the displayed total appears stale, check Formulas > Calculation Options and make sure calculation is set to Automatic. The exact menu location may vary slightly by platform or update, but formula calculation settings are available in Excel desktop.

Common problems and how to fix them

  • The criterion is missing quotation marks. Enter ">0" as the criterion. In a formula, quotation marks are part of the criteria argument and tell Excel to evaluate the comparison.
  • Zero is being included or excluded unexpectedly. Use ">0" for positive values only. Use ">=0" only when zero should count too. Use "<0" when you need the total of negative numbers.
  • Numbers are stored as text. A value that looks like 12 may have been imported as text, so it may not behave like a numeric cell in a comparison or sum. Check the source data and convert text numbers to real numbers, for example with Excel’s Convert to Number option when it is offered, or by correcting the import. Changing the cell’s number format alone does not convert text into a number.
  • The total is smaller than expected. Check that the formula covers every intended row and that the sum range is aligned with the criteria range. Also look for numbers stored as text, blank cells, or a threshold that is not actually zero.
  • Excel shows a formula error. Check that each parenthesis and quotation mark is present and that the ranges use valid cell references. Some regional settings use semicolons rather than commas as formula separators; if Excel flags the commas, use the separator required by that installation.

SUMIF generally ignores text in the cells it would otherwise add, but numeric-looking text can still cause confusing results. A useful principle is to keep calculation data numeric and reserve text for labels. If the values come from a CSV or another system, validate the imported column before relying on a total.

When SUMIFS is a better choice

SUMIF is designed for one condition. If the positive-number test must be combined with another condition, use SUMIFS, which accepts a sum range followed by one or more criteria-range and criteria pairs. For example, you might total positive quantities for one sales region.

Assume A2:A20 contains region names, B2:B20 contains quantities, and C2:C20 contains amounts to add. To total amounts for East rows where the quantity is positive, enter =SUMIFS(C2:C20,A2:A20,"East",B2:B20,">0"). The formula includes a row only when both tests are true: its region is East and its quantity exceeds zero.

Notice the argument order differs from SUMIF: in SUMIFS, the sum range comes first. With just one condition, SUMIF is often easier to read. With several conditions, SUMIFS avoids building separate formulas or manually filtering the data.

Related criteria and useful alternatives

For a positive total, the key comparison is greater than zero, not whether a cell is merely nonblank. A cell containing zero is numeric and nonblank, but it is not positive. A cell containing text is also not a positive number. This distinction matters when a worksheet mixes numbers, labels, and formulas that return empty text.

If you want to total positive values while excluding errors, first inspect why errors occur in the source range. SUMIF is not an error-cleanup tool, and errors in relevant calculation ranges can interfere with a result. Correcting the source or using a carefully designed error-handling approach is safer than hiding errors without understanding them.

For an ordinary one-condition total, SUMIF is clearer than manually adding selected cells, and it updates as the referenced data changes. A PivotTable can be more useful when you need grouped totals by category, region, or month, but it is unnecessary for a simple positive-only total. A filter can help visually review qualifying rows, while SUMIF provides a direct formula result.

Frequently Asked Questions

What is the SUMIF formula for positive numbers?
With the numbers in B2:B100, use =SUMIF(B2:B100,">0"). It adds values strictly greater than zero.

Does SUMIF include zero?
No. The criterion ">0" excludes zero. To include zero as well as positive values, use the criterion ">=0".

How do I sum one column when another column is positive?
Use the positive-value column as the criteria range and the column to total as the sum range. For instance, with quantities in A2:A20 and amounts in B2:B20, use =SUMIF(A2:A20,">0",B2:B20).

Can SUMIF add only positive numbers in a filtered list?
SUMIF evaluates the referenced cells, including rows hidden by a filter. If you need a total that changes according to which rows are visible, SUMIF alone is not the right visibility-aware calculation.

Why does SUMIF return 0 when positive values are visible?
Check that the cells are actual numbers rather than numeric-looking text, that the formula range includes those rows, and that the criterion is written as ">0". Also confirm that a separate sum range points to the intended values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.