#Invalid operation error for summing values

Romano el Polako

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?

Thanks,

Roman

Austin Urban JCI

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:

123456789

to:

'123456789

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