COUNT where cells <>"N/A"

Colin Janssen

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

Robert S.

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

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