Countifs and Column Formula issues

Stefano Ferretti

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.

Thanks

Stefano

Bob Kahn

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.

Best,

Bob

Bob Kahn

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.

Best,

Bob

Stefano Ferretti

Hi Bob,

mmm, that sounds too simple ... let me put that to test and get back to you :P

Stefano Ferretti

and it works!!!!

Thanks Bob!!!!

Bob Kahn

:) My Pleasure