Help With If Count If to Identify Unique Values in a Column

I'm trying to make a helper column to identify if a new/unique project code has been entered in a column so that I know when a new project has been created.

I've been trying to set up this formula and plan to then set up an automation to be notified when "New Project Code" has been entered into a cell. It seems to be ok on the first cell, but I keep getting the dialogue box that says there's an issue with the syntax when I try to convert it to a column formula. Anyone have an idea what needs to be adjusted?

=IF(COUNTIF([Project ALM]$1:[Project ALM]@row , [Project ALM]@row ) = 1, "New Project Code", "")

Find more posts tagged with

Formulas

Countif

Accepted answers

All comments

Kerry St. Thomas

The way your formula is set up anchors to Row 1, through the current row. That anchor is the reason you can't make it a column formula. This formula is also somewhat dependent on the current sorting of the sheet. I suggest reviewing the entire column so it's not dependent on sorting. Essentially:

=IF(COUNTIF([Project ALM]:[Project ALM], [Project ALM]@row ) = 1, "New Project Code", "")

Of course, if for any reason you recycle a Project ALM number, any EXISTING "New Project Code" flags would disappear - so if this is a problem, you can consider an alternate. Instead of using a column formula, create this column formula as an on/off flag, and use an Update Cell automation (Trigger: when a cell in this flag column changes to "Checked", Condition when "new/not new" flag IS NOT BLANK, update the "new/not new" flag to "New Project Code".) That means you'd have a helper column ("Flag column") that you could hide, and could report on this new flag controlled by the automation - the automation will populate when it's a New Code, but will only do so ONCE for each new code entered, and nothing will erase from the column.

Whichever route you choose to go, good luck and happy Smartsheeting!

Katelyn Birdsey

Exactly what Kerry said!

The error is because of the mixed use of absolute ($1) and relative (@row ) references—which column formulas don’t support. Column formulas must use only @row references. So instead of counting from the top of the column to the current row, you need to count all instances of the value in the column and flag it only if it's the first and only occurrence.

=IF(COUNTIF([Project ALM]:[Project ALM], [Project ALM]@row ) = 1, "New Project Code", "")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.