Cross-sheet formula help ...

Mel_Barnes

We currently track our Gate Adherence for each project and gate at our weekly Gate Review board. I keep metrics overall of pass, deferred pass, and fail.

We also keep Gate Adherence by department which has been a manual process thus far, however, I have written a formula that doesn't appear to be working.

I am using the "COUNTIF" formula to look at the department, and then at each Gate Date column for the presented date and look at whether it was a pass, fail, deferred pass and then count it. I have arbitrarily added one pass for August to test it but it doesn't appear to be working.

I have included a screenshot of the formula, the Gate Adherence which shows that I have added a pass for 14th August and a screenshot of what is displayed in the the formula box for August "Pass" for Strategy and Transformation department (ie. "0")

Any advice or assistance would be greatly appreciated.

Paul Newcome

Your current formula is only counting rows that have a "Pass" in ALL gate columns with dates in all Gate Date columns that are within the month of August. It is not going to count rows where only one gate has been completed within the timeframe.

To get it to count the individual gates, you would need separate COUNTIFS (one for each gate) added together.

Mel_Barnes

Amazing! Thank you! I knew it would be something simple so:

=COUNTIFS((Depart), (Date to start counting from), (Date to end counting to), (G1 pass)) + COUNTIFS((Depart), (Date to start counting from), (Date to end counting to), (G2 pass)), etc?

Paul Newcome

Basically yes.