MAX/IF +1 Formula

e.poc

Hello,

I have a sheet for our job lists, we have 3 different types of jobs, i am trying to write a formula that will pick the next highest number in each of the sections

My work flow, is chose "type of job" and that will generate 1,3 or 5 in "job classification", i am looking to pre populate the Job# with the next highest

Let me know if this doesn't make sense. Cheers!

Paul Newcome

Try inserting an auto-number column with no special formatting.

Then the Job# column would have a formula something like this...

=COUNTIFS([Auto-Number]:[Auto-Number], @cell<= [Auto-Number]@row, [Type Of Job]:[Type Of Job], @cell = [Type Of Job]@row)

e.poc

Thanks,

Sorry, if this is stupid question but which cell are you referencing with "@cell"?

Mike TV

@e.poc

The first @cell would be referencing the cell in the [Auto-Number] column which met the criteria of being less than or equal to the number in the [Auto Number]@row cell in the row the formula results were in. The second @cell is looking in the [Type of Job] column that met the criteria of being equal to the [Type of Job]@row cell.

@cell is a great way to speed up your formulas and not have to write so many references again when they're not necessary. It basically references whatever cell range that was in the formula right before it.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Paul Newcome

"@cell" just tells the formula to evaluate the previously established range on a cell by cell basis. It is relatively optional most times in COUNTIFS, but I still use it out of habit as kind of a way to say

=COUNTIFS({Range}, @cell = "Specific Text")

Count {Range} where the cell is equal to "Specific Text".