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.

Invalid data type

Vince Barnes
edited 12/09/19 in Archived 2015 Posts



I hope someone can help with a very frustrating problem. I have a sheet which is used to calculate work progressed. It takes the number of checked items in a column and sums them using the COUNTIF formula "Actual formula is ="" + COUNTIF([240mm Term]5:[240mm Term]171, 1)"


The cell underneath this take this value and subtracts it from the value in the cell above this and divides by the cell above, thus giving the percentage complete. however, this formula returns and "Invalid Type" Error. I have even tried basic subtraction and this returns the same error

=[240mm Term]173 - [240mm Term]174


Can anyone shed any light as to why I am getting this error? Is it because the COUNTIF isnt returning a number? If so how can I convert the result tof COUNTIF to a number?


many thanks




  • I should also add that the column has a conditional format so when the cell is checked it turns from Red to Green and it is a checkbox type cell


  • Travis
    Travis Employee
    edited 11/20/15

    Hi Vince, the formula result is formatted as a Text value because of the "" + you included at the front of the formula. 


    Two options: remove the "" +  Is there a reason you included this? If you are not concatenating then it is unnecessary.


    Another option is to format the result as a number in your second formula. Like this:


    =VALUE([240mm Term]173) - [240mm Term]174


    VALUE() will turn a text value into a number (assuming its only digits and not letters). 

This discussion has been closed.