Converting a formula to a "column formula"

Sarah.S

I am trying to convert this formula into a column formula.

It takes selected cells from a row to populate the overall "Status" column. If tasks are "Not Started", the overall status will be "Not Started". The Same would apply for Cancelled. If all tasks are complete, cancelled or n/a, the overall status would be "n/a". If any tasks are In Progress the overall status is "In Progress". And if they are all blank, the overall status is "Not Started".

=IF(COUNTIFS(PM2:Warehouse2, "Not Started") = COUNT(PM2:Warehouse2), "Not Started", IF(COUNTIFS(PM2:Warehouse2, "Cancelled") = COUNT(PM2:Warehouse2), "Cancelled", IF(COUNTIFS(PM2:Warehouse2, OR(@cell = "Complete", @cell = "Cancelled", @cell = "n/a")) = COUNT(PM2:Warehouse2), "Complete", "In Progress")))

As it is right now, I am not able to convert this formula into a column formula. What changes do I need to make to convert it? Is it possible?

Because it only references certain cells in a row, is it possible to change part of this formula to @cell or @row?

Thank you for your help!

Kelly Moore

Hello @Sarah.S

Yes, changing to @row will allow your formula to be converted to a column formula.

=IF(COUNTIFS(PM@row:Warehouse@row, "Not Started") = COUNT(PM@row:Warehouse@row), "Not Started", IF(COUNTIFS(PM@row:Warehouse@row, "Cancelled") = COUNT(PM@row:Warehouse@row), "Cancelled", IF(COUNTIFS(PM@row:Warehouse@row, OR(@cell = "Complete", @cell = "Cancelled", @cell = "n/a")) = COUNT(PM@row:Warehouse@row), "Complete", "In Progress")))

You can find more information on the @row function here

cheers

Kelly Moore

Hello @Sarah.S

Yes, changing to @row will allow your formula to be converted to a column formula.

=IF(COUNTIFS(PM@row:Warehouse@row, "Not Started") = COUNT(PM@row:Warehouse@row), "Not Started", IF(COUNTIFS(PM@row:Warehouse@row, "Cancelled") = COUNT(PM@row:Warehouse@row), "Cancelled", IF(COUNTIFS(PM@row:Warehouse@row, OR(@cell = "Complete", @cell = "Cancelled", @cell = "n/a")) = COUNT(PM@row:Warehouse@row), "Complete", "In Progress")))

You can find more information on the @row function here

cheers

Sarah.S

@KDM Thank you! That worked perfectly.