Trying to accurately gather % complete based on statuses and excluding N/A in children rows

Hi all!

I am revamping a project task list template that my team uses each time we are building a new location. We'd like a high level completion status for each department involved in these projects, but I'm running into a bit of a hiccup with my formula.

In the Status column of the departmental parent rows, I am using the following formula:

=COUNTIF(CHILDREN(Status@row), "Completed") / COUNT(CHILDREN([Task Name]@row))

However, I'm realizing that I need to exclude anything that has a status of "N/A" from my counts in order to project a correct % complete.

I have no idea if I'm just exhausted or what's going on, but I can't for the life of me adjust my Status bar formula to correctly count the completed items and divide them by the total number of tasks, excluding those that are N/A, to get an accurate %.

Any help is appreciated!