COUNTIFS and/or IFS help needed
Here is my situation:
I have a PMO tracking sheet that is tracking many projects through various statuses. I am putting together a metrics sheet so I can create charts to put on a dashboard.
In the metric sheet I am trying to count the number of projects by department where the status is not Complete or Cancelled.
I can't get the formula right...
Here is what I am trying to at least exclude Complete projects, but banging my head against the wall. Why can't there be a formula wizard/builder??
=IF(({StatusRange}<>"Complete")),(COUNTIFS({DepartmentRange}, Counts@row),)
Comments
-
You could try (Department1 being QM and Department2 being CD)
=COUNTIFS($DepartmentRange$1:$DepartmentRange$6, "Department1", $Status$1:$Status$6, <>"Complete", $Status$1:$Status$6, <>"Cancelled")
=COUNTIFS($DepartmentRange$1:$DepartmentRange$6, "Department2", $Status$1:$Status$6, <>"Complete", $Status$1:$Status$6, <>"Cancelled")
-
If you wanted to keep a running total in 1 column, you could try something similar to this:
=IF(Department@row = "QM", COUNTIFS(Department$1:Department1, "QM", Status$1:Status1, NOT(OR(@cell = "Cancelled", @cell = "Complete"))), IF(Department@row = "SD", COUNTIFS(Department$1:Department1, "SD", Status$1:Status1, NOT(OR(@cell = "Cancelled", @cell = "Complete"))), IF(Department@row = "CD", COUNTIFS(Department$1:Department1, "CD", Status$1:Status1, NOT(OR(@cell = "Cancelled", @cell = "Complete"))))))
-
I'll give these a whirl!! Thanks for the replies
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!