Countifs and Column Formula issues

I have quite a puzzle.

I am using an if + countifs formula with absolute values in order to return a sequential numbering of items.

having absolutes means I cannot make this a column formula.

Data in the sheet will be added by a form, which means I will have to manually copy the formula everytime I have a new entry, which isn't ideal.

Is there a way to get to the same result automatically?

Example Below :

In the above example, the first 4 columns will be filled in via form

column "Level" has a simple column formula, that will return either 1 or 2 depending if the Business Outcome (lev1) has a value or not.

The 'Auto Level' column is the painful one.

The formula here is :

=IF(Level@row <> "", IF(Level@row = 2, COUNTIFS($[Account Name]$1:[Account Name]@row, [Account Name]@row, $[Executive Name]$1:[Executive Name]@row, [Executive Name]@row) - 1, 0))

essentially this formula will give me sequential numbers for all the entries that have same Account Name and same Executive Name where the Level column is 2.

the formula works, but I need to be able to have it populated automatically when new data is input in the sheet.



Best Answer

  • Bob Kahn
    Bob Kahn ✭✭✭
    Answer ✓

    Hi Stefano,

    As long as the primary column of the new row is populated and there are no blank rows at the end of your sheet the formula will copy down automatically. Using a form for new entries is perfect since it ensures rows will be added to the immediate end with no blank rows.

    You might want to try to delete the blank rows at the bottom just in case there is data that you cant see.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!