COUNTIFS and/or IFS help needed

@CollabMeganPMP

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),)

Gil Nash

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")

Gil Nash

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"))))))

Paul Newcome

Try something like this...

 

=COUNTIFS({DepartmentRange}, Counts@row, {StatusRange}, AND(@cell <> "Complete", @cell <> "Cancelled"))

@CollabMeganPMP

I'll give these a whirl!! Thanks for the replies