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.

COUNT where cells <>"N/A"

Options
Colin Janssen
Colin Janssen ✭✭✭
edited 12/09/19 in Archived 2017 Posts

Hi,

I'm struggling to combine a SUM and a COUNT together (keeps returning an error).

=SUM([CELL 1]22:[CELL 4]22) / COUNTIF([CELL 1]22:[CELL 4]22, <>"N/A")

This returns a #DIVIDE BY ZERO.

CELL 1 to CELL 4 could contain the numbers 1 to 5 or N/A.

The end result is to provide a percentage out of 100% for the total cells with a number.

Sure I'm doing something stupid :)

Any assistance would be appreciated.

Colin

Tags:

Comments

  • Robert S.
    Robert S. Employee
    edited 12/19/17
    Options

    Hello Colin,

     

    Thanks for the question, this one's a little trickier. How you have this formula written will work if the cells contained text rather than numbers. Text and Numbers are seen differently within Smartsheet, so since the only text value in your referenced cells is "N/A" which is not being counted, it results in a zero. The formula then divides by that zero and gives the #DIVIDE BY ZERO error.

     

    Since you're looking to have the count only count cells in this range that are numbers, you can use the ISNUMBER() and @cell functions to resolve this. More on ISNUMBER() can be found here (https://help.smartsheet.com/function/isnumber), and more on @cell can be found here (https://help.smartsheet.com/articles/2476491). This is what your formula would look like with that change:

     

    =SUM([CELL 1]22:[CELL 4]22) / COUNTIF([CELL 1]22:[CELL 4]22, ISNUMBER(@cell))

     

    This formula will give you an average of the numbers in the referenced cells. From what you explained however, it looks like you're actually wanting something different. If you're looking for a total score percentage out of 100%, where it only counts the cells that are numbers and each cell is out of 5, that formula would look more like this:

     

    =SUM([CELL 1]22:[CELL 4]22) / (COUNTIF([CELL 1]22:[CELL 4]22, ISNUMBER(@cell)) * 5)

     

    Keep in mind that with either of these formulas, if all of the referenced cells are set to "N/A" the result will be the #DIVIDE BY ZERO error. If you'd like to account for this, you can use the IFERROR() function to either leave it blank or make it say something else. More on this function can be found here if needed (https://help.smartsheet.com/function/iferror).

  • Colin Janssen
    Options

    Thanks...that worked perfectly.

    Essentially changed it too the below to cater for the error

     

    =IFERROR(SUM([COLUMN 1]20:[COLUMN 2]20) / (COUNTIF([COLUMN 1]20:[COLUMN 2], ISNUMBER(@cell)) * 5), "N/A")

This discussion has been closed.