Converting a formula to a "column formula"
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
-
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
Answers
-
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
-
@KDM Thank you! That worked perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!