#Invalid operation error for summing values

Hello Team!

I have a weird case, but let's go straight to the point.

I have a simple formula:

=SUMIF([F Tracking ID Number]:[F Tracking ID Number], [F Tracking ID Number]@row, [X ASIN Value (CI)]:[X ASIN Value (CI)])

which basically sums values from column X for duplicate values in column F.

The formula works fine for "short" entries in column F, but gives #INVALID OPERATION error for "longer entries, see:

Have you ever experienced something like this? Is there a way to overcome it?




  • I'm guessing the issue is with the data-type. Your longer entries don't have alphabetical characters and are probably seen as data values. I am not sure where you would implement this, but it may need to be a new column:

    =IF(ISNUMBER([F Tracking ID Number]@row)=TRUE,[F Tracking ID Number]@row + "",[F Tracking Number]@row)

    This would add a Text format 'blank' at the end of the cell, converting a Value to Text

    For example, if I had a column [Numbers] and in a new column put [Numbers]@row + "Hi", it would convert the new column to a text format with the value you had in [Numbers] column and appending "Hi".

    You could manually test if this is the issue by adding an apostrophe prior to the 'long' names:




    This will change the value to text and you can see if it starts working/picking up the problem columns

  • Hey @Austin Urban JCI

    Interesting, i will give it a try and let you know, thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!