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.
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?
Try
=SUM(SUMIF(INDIRECT({“I13:I18″,”I10:I11″,”I8″}),”>0”))
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.
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"))