My formula is getting a syntax error when I try to apply to column

drivera

Hi everyone,

I am getting a syntax error when applying a formula to a column when the formula works when I use it. I am trying to use a helper column to identify unique order numbers for a sheet that will be continuously updated.

=IF(COUNTIF([Original Order #]$1:[Original Order #]@row, [Original Order #]@row) = 1, [Original Order #]@row, "")

Paul Newcome

It is because of the direct cell reference instead of @row.

Try this instead… Insert an auto-number type column (called "Auto" in this example). Then insert a text/number column (called "Row" in this example) and use this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then your COUNTIFS would adjust to:

=IF(COUNTIFS([Original Order #]:[Original Order #], @cell = [Original Order #]@row, Row:Row, @cell <= Row@row) = 1, [Original Order #]@row, "")

drivera

Hey Paul, I appreciate your assistance! I followed your method and it seemed to work but only partially. For some reason the formula then only grabbed certain Original Order #'s and not every instance of a unique order #. I will keep fiddling with it but thank you again.

Humashankar

Great take