Making CountIFS and SUMIFS Formula Dynamic

I have a workbook with lots of SUMIFS and COUNTIFS formula. I now need to add a variable criteria to all of them, but to go through and do this manually will take forever as there are hundred of them.

So I baiscally have a basic formula:

Please Login or Register  to view this content.

On my source data I have a column which can be filtered by 'Yes' or 'No' and doing so affects the totals displayed. What I want to be able to do is to effectively do is find a way for all the SUMIFS formula to auto update based on whether column Z is filtered by Yes or No, so they return one value if Yes and another value if No, so effectively adding another criteria range and criteria to every instance of SUMIFS, but to update hundreds of formula manually is going to take ages.

I guess in reality you would be saying:

Please Login or Register  to view this content.

Is there any other quick and easy way to achieve this making it variable without having to manually update every single instance of SUMIFS.

Many thanks