Out of Service Formula

Bryan Letourneau

Hi Community,

I'm attempting to create a formula showing the OOS status of the most recent submission for each asset based on the Asset ID in form submissions. For example, below Asset ID FD-001201 would be considered out of service, however, I'm not sure how to account for it being the most recent submission if there had been a previous entry where the OOS Tag had been removed.

My assumption is the the formula would look something like IF(Countif(Asset ID = FD-001201), "No", "Yes", if(and(countif(asset ID = FD-00120), OOS Tag Removal Date "Blank", "Yes", "No")) but this wouldn't account for only the most recent submissions.

If someone knows how to account for ONLY most recent submission for a given Asset ID or another way to write the formula, it would be greatly appreciated.

Thanks!

Jeff Reisman

@Bryan Letourneau

You'll want to use a MAX/COLLECT to find the latest "Date OOS tag Applied" date for that Asset ID, and then use that "Date OOS tag Applied" date and the AssetID to determine if the "OOS Tag Removal Date" cell has a value in it (you can use ISBLANK or ISDATE for this.)

Put this is a date-type column and make sure it works. (Replace the local ranges with remote ranges if looking these up from another sheet.)

=MAX(COLLECT([Date OOS tag Applied]:[Date OOS tag Applied], [Asset ID]:[Asset ID], [Asset ID]@row))

Once that works, you can use that inside an IF:

=IF(COUNTIFS([Date OOS tag Applied]:[Date OOS tag Applied], MAX(COLLECT([Date OOS tag Applied]:[Date OOS tag Applied], [Asset ID]:[Asset ID], [Asset ID]@row)), [Asset ID]:[Asset ID], [Asset ID]@row, [OOS Tag Removal Date]:[OOS Tag Removal Date], ISBLANK(@cell)) > 0, "Yes", "No")

In English: If the count of rows where: the "Date OOS tag Applied" is the latest date for the Asset ID on this row, the Asset ID is the same as the Asset ID on this row, and the "OOS Tag Removal Date" is blank - is greater than 0, then "Yes" it is OOS; otherwise, "No" it is not OOS.

Bryan Letourneau

Nevermind, I needed a parenthesis after the great than 0 part. Thank you so much!

Jeff Reisman

In your last criteria, it should be ISBLANK(@cell). Don't put the {OOS Service Log Range 3} range there, just need ISBLANK(@cell).

Jeff Reisman

@Bryan Letourneau

You'll want to use a MAX/COLLECT to find the latest "Date OOS tag Applied" date for that Asset ID, and then use that "Date OOS tag Applied" date and the AssetID to determine if the "OOS Tag Removal Date" cell has a value in it (you can use ISBLANK or ISDATE for this.)

Put this is a date-type column and make sure it works. (Replace the local ranges with remote ranges if looking these up from another sheet.)

=MAX(COLLECT([Date OOS tag Applied]:[Date OOS tag Applied], [Asset ID]:[Asset ID], [Asset ID]@row))

Once that works, you can use that inside an IF:

=IF(COUNTIFS([Date OOS tag Applied]:[Date OOS tag Applied], MAX(COLLECT([Date OOS tag Applied]:[Date OOS tag Applied], [Asset ID]:[Asset ID], [Asset ID]@row)), [Asset ID]:[Asset ID], [Asset ID]@row, [OOS Tag Removal Date]:[OOS Tag Removal Date], ISBLANK(@cell)) > 0, "Yes", "No")

In English: If the count of rows where: the "Date OOS tag Applied" is the latest date for the Asset ID on this row, the Asset ID is the same as the Asset ID on this row, and the "OOS Tag Removal Date" is blank - is greater than 0, then "Yes" it is OOS; otherwise, "No" it is not OOS.

Bryan Letourneau

Hi Jeff,

Thank for you the reply. I think the formula you are providing would be used on the submission sheet, where as I need the formula to reference the submission sheet such as below:

So the sheet being referenced is a submission sheet from a form input which can house many different asset ID's based on submission. I don't think I'm doing a great job of explaining it.

Bryan Letourneau

The max collect portion works separately however, when nested I get the #unparseable error

I think I might be missing a parenthesis somewhere?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/329932#Comment_329932

Looks like an extra parentheses at the end. See how the very last end parentheses is black, but the very first open parentheses is blue?

Bryan Letourneau

I've deleted the last parenthesis but still receive the error

Jeff Reisman

https://community.smartsheet.com/discussion/comment/329935#Comment_329935

Ah - the function should be COUNTIFS, not COUNTIF. You're giving it multiple criteria so it needs to be COUNTIFS.

Bryan Letourneau

I changed to countifs then noticed I was missing a " after the last "No". But now I'm getting #incorrect argument

Bryan Letourneau

Nevermind, I needed a parenthesis after the great than 0 part. Thank you so much!

Jeff Reisman

In your last criteria, it should be ISBLANK(@cell). Don't put the {OOS Service Log Range 3} range there, just need ISBLANK(@cell).

Bryan Letourneau

That solved it, thank you sir!