How to create this COUNTIFS with AND/OR/NOT

Megan Kauffman

I cannot figure out how to create this formula to get the metrics. I can get each individual dept metric correct using =IFERROR(COUNTIFS({(Phoenix) Project Tracker - Aerial Status}, AND(@cell <> "No", @cell <> "Complete"), {(Phoenix) Project Tracker - Status column}, AND(@cell = "Complete"), {(Phoenix) Project Tracker - County}, AND(@cell = "Hills"), {(Phoenix) Project Tracker - Inv Status}, AND(@cell = "")), "")

HOWEVER I need all depts as 1 metric for a corporate overview. I've created the report and the filters are in the attachment; I just can't seem to translate to code properly :(

Complete job; ongoing dept filter.pdf

Megan Kauffman

Maybe I'm going about it all wrong... If Status = Complete then each dept (Aerial; UG; Coax SPL; Fiber SPL; MDU needs to be Complete or No; I'm looking for the count of all the projects that don't meet this criteria.

Kelly Moore

Hello @Megan Kauffman

Here's one approach that I believe will work. Whenever I am evaluating multiple cells ACROSS A ROW, I add a helper column to do that evaluation row by row. Then I do a count of the helper column. I am using a checkbox column as my helper column. Name it what you like.

This is the formula for the helper column. It first looks at your Status column to see if the status is 'Complete'. If true, the formula looks across the row to count the number of Completes and No's. As written this formula is looking for instances where the count of departments containing the words "Complete" or "No" is not equal to the total number of departments. The checkbox checks these instances.. If you wanted the inverse (all departments are complete), you would have the checkmark check when the values equaled the total number of departments.

HELPER COLUMN =IF(Status@row = "Complete", IF(COUNTIFS([Aerial]@row:[MDU]@row, "Complete") + COUNTIFS([Aerial]@row:[MDU]@row, "No") <> 7, 1))

This does assume that your hidden columns do not contain "Complete" or "No" as a cell value. If they do, you must write out each column individually and add it to the countifs formula. (The terms within a COUNTIFS are already 'And' conditions so one does not include AND in the formula).

In your metric sheet (or a Sheet summary field) you should be able to use this formula to gather your count of departments not equal to "Complete" or "No".

Metric Sheet = COUNTIFS({data sheet HELPER COLUMN}, 1)

Would this work for you?

Kelly