Count IF Question - Running Count

Good Morning All,

Currently have this column formula called "Count AD": =COUNTIF([Ad Name]$1:[Ad Name]@row, [Ad Name]@row) on lets say Sheet B

I have the same formula on Sheet A: =COUNTIF([Creative Name]$1:[Creative Name]@row, [Creative Name]@row)

Please Note: Sheet A Creative Name = Sheet B Ad Name

Sheet B is generated with a Data Shuttle Process where is mapping "Ad Name" and numerous other columns from a Excel Document attached to the Smartsheet. The "Count AD" Column on Sheet B is in place to count the Ads if they appear twice in the Excel File. So if row 1 and row 45 have the same ad name, the Count AD Column will count 1 for the first time it appears on row 1 and count 2 for row 45. The Count Ad Column Formula on Sheet A is doing the same thing. These Count AD Column formulas are what I using to create a unique identifier to pull information from Sheet A to Match back to Sheet B (Sheet B is essentially an import/export file to ensure what we have in Smartsheets aligns with what is running on our platforms (google/Meta))

THE PROBLEM: the data shuttle process the populates Sheet B is set to run whenever a new attachment is added. However whenever the data shuttle process runs it is erasing my Column Ad formula and I have to re enter it and drag it down for all my rows (which can range from 50-1900)

Is the data shuttle process causing this formula to keep going away after it runs? Or is there a better formula I can use so that I can turn it into a column formula? I cannot do it currently since my formula references "[Creative Name]$1".

Any tips tricks or pointers are welcome! I asked a similar question in communities that uses the "Count AD" Column as well to actually pull in my Unique Identifier between sheet A and sheet B so if this formula breaks when the Data shuttle processes for Sheet B then my index collect to pull my unique identifier breaks as well. That question was titled "Index Collect or Lookup Formula Help


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!