Count IF Question - Running Count

ConnorForm

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

"

Paul Newcome

Use an auto-number column with no special formatting then adjust your COUNTIFS to allow it to be a column formula:

=COUNTIFS([Ad Name]:[Ad Name], @cell = [Ad Name]@row, [Auto-Number]:[Auto-Number], @cell<= [Auto-Number]@row)

Paul Newcome

The auto-number column applies a number (when no special formatting is used) to each row incrementing by one and never repeating as new rows are added. Think of it as 1st row added, 2nd row added, so on and so forth. You are basically counting how many rows have the same [Ad Name] and also came before "@row".

Paul Newcome

Use an auto-number column with no special formatting then adjust your COUNTIFS to allow it to be a column formula:

=COUNTIFS([Ad Name]:[Ad Name], @cell = [Ad Name]@row, [Auto-Number]:[Auto-Number], @cell<= [Auto-Number]@row)

ConnorForm

@Paul Newcome Wow That worked!!! Trying to wrap my head around how the auto number is working in my favor here. But none the less!!

=COUNTIFS([Ad Name]:[Ad Name], @cell = [Ad Name]@row, [Auto-Number]:[Auto-Number], @cell<= [Auto-Number]@row)

Giving the same results as my old Count Ad column was!! Thank you so much!!

Paul Newcome

The auto-number column applies a number (when no special formatting is used) to each row incrementing by one and never repeating as new rows are added. Think of it as 1st row added, 2nd row added, so on and so forth. You are basically counting how many rows have the same [Ad Name] and also came before "@row".