Auto Generated Number formula based on value in another column

DoraExplorer

I need help with a formula:

Need auto-generated number based on:

If value in "type" column is "Type1", auto-generate number in column "number" , otherwise, leave blank

Cannot use Auto-Generate number column because of criteria

Can make column formula to ensure auto-generated number is in new and existing rows

Danielle Arteaga

This prior answer from @Genevieve P. may be what you are after: https://community.smartsheet.com/discussion/85019/creating-a-custom-number-formula-with-conditions

Hope this helps!

DoraExplorer

Thanks @Danielle Arteaga - I think @Genevieve P. formula is a bit more detailed and complex than what I need. See below, my request, may be easier with a visual

Number does not have to be consecutive, since new rows may be added - I just don't want them duplicated.

Colleen Patterson

I think that you would be able to layer in an IF Statement, and use a helper auto system created date column.

I would use a auto system created date for the date column

Formula for the count column

=IF(Type@row = "Type 1", COUNTIFS(Type:Type, Type@row, Date:Date, <=Date@row), " ")

This will look first to match the type, then run the count if formula, then if not Type 1, will leave blank)

DoraExplorer

Thanks @Colleen Patterson - but why would I need a date column? Did you mean auto number column?

Colleen Patterson

@DoraExplorer, the date column will help the formula look at when they were entered, so that if the sheet somehow gets sorted, they will remain as numbered, and not renumber based on the sort. It is to ensure that the data is pervasive, as a just in case. Hope that helps!