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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!