Converting a formula to a "column formula"

03/10/21
Accepted

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!

Best Answer

  • Kelly MooreKelly Moore ✭✭✭✭✭
    Accepted Answer

    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([email protected]:[email protected], OR(@cell = "Complete", @cell = "Cancelled", @cell = "n/a")) = COUNT([email protected]:[email protected]), "Complete", "In Progress")))

    You can find more information on the @row function here

    cheers

Answers

  • Kelly MooreKelly Moore ✭✭✭✭✭
    Accepted Answer

    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([email protected]:[email protected], OR(@cell = "Complete", @cell = "Cancelled", @cell = "n/a")) = COUNT([email protected]:[email protected]), "Complete", "In Progress")))

    You can find more information on the @row function here

    cheers

  • @KDM Thank you! That worked perfectly.

Sign In or Register to comment.