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

by Tom Urtis 4 Comments ↓

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.

‹ Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Tom’s Tutorials For Excel: Calculating the Date for Easter ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

4 comments on “Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text”

1. Ashley Stewart says:

   September 19, 2017 at 12:31 pm

   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?

   Reply
   • Tom Urtis says:

     September 19, 2017 at 1:30 pm

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

     Reply
     • sandeep kothari says:

       October 19, 2019 at 11:52 pm

       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.

       Reply
       • Tom Urtis says:

         October 20, 2019 at 9:31 am

         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"))

         Reply