Multiple Counts of Project Type Column but Exclude if Complete from Status Column
I'm trying to count in my 'Type' column those labeled as "Business Planning", "HRO", "Operational Transformation", "Analytic Support" and "Strategic Implementation" while EXCLUDING those labeled as "Tasks" and then I want to EXCLUDE those 'Types' that are marked as 'Complete" or "Canceled' and only capture those labeled as "In Progress "NotStarted" "On going" or "On Hold"
I got this far in capturing types: =COUNTIF(Type:Type, HAS(@cell, "HRO")) + COUNTIF(Type:Type, HAS(@cell, "Business Planning")) + COUNTIF(Type:Type, HAS(@cell, "Operational Transformation")) + COUNTIF(Type:Type, HAS(@cell, "Analytic Support")) + COUNTIF(Type:Type, HAS(@cell, "Strategic Implementation"))
But I can't figure out how to now add in the piece of status and excluding those labeled "Complete' or 'Canceled' - Any help please??
Best Answer
-
Hey @Megan Hart
Give this a shot (where 'Status' is the name of your second column)
=COUNTIFS(Type:Type, <>"Tasks", Status:Status, <>"Complete", Status:Status, <>"Cancelled")
-MCS
Answers
-
Hey @Megan Hart
Give this a shot (where 'Status' is the name of your second column)
=COUNTIFS(Type:Type, <>"Tasks", Status:Status, <>"Complete", Status:Status, <>"Cancelled")
-MCS
-
You need to look at a COUNTIFS formula. That would be where you need to look. You could also use the not equal to simplify your formula if you are only excluding one type. The piece for that part would look like this:
Type:Type, <>"Tasks"
Your COUNTIFS formula would look something like this:
=COUNTIFS(Type:Type, <>"Tasks", Status:Status, OR(@cell <> "Complete", @cell<> "Canceled"))
See if that works.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
THIS WORKED!!! Thank you SO much for the help.
-
Got it working, thank you!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!