Excel Sum If Not Zero: Formula & Examples

Formulas & functions · Updated November 22, 2026

To sum only cells that are not zero in Excel, use the SUMIF function with the criteria "<>0". For example, =SUMIF(A1:A10,"<>0") adds all values in A1:A10 that are not equal to zero. This works for numbers, blank cells are ignored, and it handles both positive and negative non-zero values.

Why Would You Want to Exclude Zeros?

Zeros often appear in spreadsheets as placeholders, default values, or results of formulas that return 0 when no data is entered. When you calculate totals, averages, or other aggregates, these zeros can skew your results. For instance, if you track monthly expenses and some months have no spending, a simple SUM would include those zero months, which might be fine, but if you want the total of actual spending only (ignoring months with no activity), you need to exclude zeros. Similarly, in performance dashboards, a zero might represent a missing value, and excluding it gives a more accurate picture.

Using SUMIF to Sum Non-Zero Values

The SUMIF function is the most straightforward way. Its syntax is =SUMIF(range, criteria, [sum_range]). When you omit sum_range, it sums the values in range itself. The criteria "<>0" means “not equal to zero”.

Example: Suppose you have sales figures in cells B2:B10. To sum only the non-zero sales, use:

  • Formula: =SUMIF(B2:B10,"<>0")
  • If B2:B10 contains {100, 0, 50, 0, 75, 0, 20, 0, 30}, the result is 275 (100+50+75+20+30).

If your data has a separate criteria range, for example, you want to sum values in C2:C10 where the corresponding value in B2:B10 is not zero, use =SUMIF(B2:B10,"<>0",C2:C10). This sums only the C values that align with non-zero B values.

Using SUMIFS for Multiple Conditions

If you need to exclude zeros while also applying other criteria, use SUMIFS. The syntax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). To exclude zeros, add a condition on the same range you are summing or on another range.

Example: Sum values in C2:C10 where B2:B10 is not zero and A2:A10 equals “Product A”.

  • Formula: =SUMIFS(C2:C10, B2:B10, "<>0", A2:A10, "Product A")
  • If C2:C10 has {10,20,30,40,50}, B2:B10 has {0,5,0,5,0}, and A2:A10 has {“Product A”,”Product A”,”Product B”,”Product A”,”Product B”}, the result is 20+40=60.

Alternative: SUMPRODUCT for Complex Logic

For more control, especially when you need to exclude zeros but also handle text or errors, SUMPRODUCT can be used. The formula =SUMPRODUCT((A1:A10<>0)*A1:A10) multiplies each value by a boolean (TRUE/FALSE) that is TRUE when the cell is not zero. TRUE is treated as 1, FALSE as 0, so only non-zero values are added.

Example: With A1:A10 = {5,0,3,0,0,7,0,2,0,0}, the formula returns 17 (5+3+7+2).

This method also works when you need to exclude zero from multiple ranges, for example, =SUMPRODUCT((A1:A10<>0)*(B1:B10<>0)*A1:A10) sums A values where both A and B are non-zero.

Common Pitfalls and Misconceptions

One common mistake is using =SUMIF(A1:A10,"<>0") when your data contains text or blank cells. SUMIF with “<>0″ will ignore text and blanks, which is usually fine. However, if your data contains the text “0” (as a string), it will be treated as zero because SUMIF converts text numbers to numbers. Another pitfall: using <>0 without quotes in the criteria will cause an error; always enclose the criteria in double quotes.

Another misconception is that SUMIF with “<>0″ will exclude only zeros, but it also excludes blank cells. If you want to include blanks as zeros, you would need a different approach, such as =SUM(A1:A10) which treats blanks as 0.

When to Use SUM vs SUMIF

Use SUM when you want to add all numeric values, including zeros. Use SUMIF with “<>0″ when zeros are placeholders or irrelevant to your calculation. For example, if you are calculating average order value and have orders with zero value (e.g., returns), you might exclude them to get the average of actual sales. But if you are calculating total inventory, zeros are meaningful (out of stock) and should be included.

Also consider using SUMIF to sum non-zero values in a filtered list: if you apply an AutoFilter, SUBTOTAL functions are better, but SUMIF works on the entire range regardless of filtering.

Frequently Asked Questions

Can I sum only positive non-zero numbers? Yes, use =SUMIF(A1:A10,">0") to sum positive numbers only. To sum negative non-zero numbers, use =SUMIF(A1:A10,"<0").

How do I sum non-zero cells in a row? The same formula works horizontally: =SUMIF(A1:F1,"<>0") sums all non-zero cells in row 1.

What if my data has errors like #DIV/0! SUMIF will ignore errors in the range if the criteria is "<>0", but if an error is in the sum_range (when using a separate sum_range), it will return an error. To avoid that, use AGGREGATE or SUMPRODUCT with error handling, but that is more complex.

Does SUMIF with "<>0" work with dates? Yes, dates are stored as numbers, so a date like 1/1/2020 (serial number 43831) is non-zero and will be included. If you have blank cells, they are ignored.

Can I use a cell reference for the criteria? Yes, you can put the criteria in a cell, say D1, and use =SUMIF(A1:A10,D1) where D1 contains "<>0". This makes it easy to change the criteria without editing the formula.

Is there a way to sum non-zero values in Excel for the web? Yes, SUMIF and SUMIFS work in Excel for the web. The syntax is identical to desktop Excel.

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.