Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula To Ignore Zeros In Averages?

Brian Andrews
edited 12/09/19 in Archived 2015 Posts

I have columns running left to right with the 12 months listed as the main header of each. I want to have a column on the far right that shows our average month's sales based on each months totals we go. RIght now it is May, but the simple AVG formula also counts June-December sicne they have zeros in them. So, instead of it dividing the total by 5, it is dividing by 12, thus giving a false average. I already understand that I can simply delete the zeros. However, those zeros are there based on the formula with that cell which will total that column's sales totals based on the cells below (Children).


In short, I just need a way to tell the formula to ignore anything showing a zero and then divided by the cells containg actual sales numbers. 


So, what would you add to this is formula below to accomplish this?







  • Travis
    Travis Employee

    Hi Brian! This is possible by creating a formula that will sum the range of numbers and divide that number by a count of the cells subtracted by a count of the cells that contain 0. 


    Here is what the formula would look like if your column names and range are January - December (and in row 3):


    =(SUM(January3:December3)) / ((COUNT(January3:December3)) - (COUNTIF(January3:December3, 0))) 


    This formula will divide the total sum by the number of cells that do not contain a 0. 



  • Yes, it worked! Thanks so much Travis.

  • By the way, some lines will have zeros in almost all months until later in the year. It looks like this particular formula requires at least one cell to contain a number greater than zero. Otherwise, it shows #DIVIDE BY ZERO. Any addtions I could add to the formula to force it to show a zero in that cell in place of that message?

  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭
    edited 05/15/15

    To avoid seeing the error codes because there is no data to crunch, eg Divide by Zero you can insert and IF statement. Detailed explanations in the "formula errors section of the help pages with workarounds for most of these. :) 

  • Travis
    Travis Employee

    Brian try this formula which will first check if the count is the same as the count if 0. If these are the same, the formula will produce a "0". If they are not the same, it will move on the formula I provided above. 


    =IF((COUNT(January3:December3)) = (COUNTIF(January3:December3, 0)), "0", SUM(January3:December3) / ((COUNT(January3:December3)) - (COUNTIF(January3:December3, 0))))

  • Thanks again!


    By the way, how would I make it display the zero like this: $0.00 rather than this: 0.


    When I try to add "Currency Format" using the control panel to the left, it won't change. I figured something in this formula must be overrriding it.

  • Travis, any ideas on my question above?

  • Travis
    Travis Employee

    Brian, thanks for following up! The 0 is formatted as text (and cannot be currency) because I added quotes around the 0 (not sure why I did that! Undecided). If you remove the quotes, it will be formatted as a number and then will be displayed as $0.00. Here is the new formula with the change:



    =IF((COUNT(January3:December3)) = (COUNTIF(January3:December3, 0)), 0, SUM(January3:December3) / ((COUNT(January3:December3)) - (COUNTIF(January3:December3, 0))))

  • Of course, in the end it's always something simple and easy in these instances. All fixed now. Many thanks!

This discussion has been closed.