MAX/IF +1 Formula
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!
Answers

Try inserting an autonumber column with no special formatting.
Then the Job# column would have a formula something like this...
=COUNTIFS([AutoNumber]:[AutoNumber], @cell<= [AutoNumber]@row, [Type Of Job]:[Type Of Job], @cell = [Type Of Job]@row)

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

The first @cell would be referencing the cell in the [AutoNumber] 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.

"@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".
Help Article Resources
Categories
Check out the Formula Handbook template!