Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text

Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text

When you have a mixed list of cells that contain negative numbers, positive numbers, text, and blanks, here is a formula to calculate the average for only the positive numbers.

In the picture, the formula in cell B2 is…
=SUMIF(B5:B23,">0")/COUNTIF(B5:B23,">0")
…which ignores negative numbers, zeroes, text, and blank cells.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
4 comments on “Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text
  1. Ashley Stewart says:

    I’m trying sum a column but not every cell is included in the sum as well as excluding the negative totals. I’m not sure what I’m doing wrong. I’ve tried it with SUM & SUMIF
    =SUM(I13:I18,I10:I11,I8,”>0″)

    What else should I try or do?

    • Tom Urtis says:

      Try
      =SUM(SUMIF(INDIRECT({“I13:I18″,”I10:I11″,”I8″}),”>0”))

      • sandeep kothari says:

        Dear Tom
        I tried this formula =SUM(SUMIF(INDIRECT({“I13:I18″,”I10:I11″,”I8″}),”>0”))
        but excel is not accepting it & excel is showing the message: “There is a problem with this formula”. Please check.

        • Tom Urtis says:

          The formula works but its double quote characters do not translate properly in html on these web pages. There are 8 double quote characters in the formula. When you copy the formula from this web page to your worksheet, Manually replace each one with the double quote character on your keyboard and that will fix the problem.

          =SUM(SUMIF(INDIRECT({"I13:I18","I10:I11","I8"}),">0"))

Leave a Reply to Tom Urtis Cancel reply

Your email address will not be published. Required fields are marked *

*