CURRENT FORMULA (Erroring)
=countifs(Q1L!$E$7:$H$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)
(The only active reference by color is "Q1M!O35")

GOAL: Use countifs to count cells marked "x" in rows matching a second matching reference to show numbers marked.

CONTEXT: "x" is used to mark cells in columns to categorize log entries. Another column delineates departments to which each log entry belongs. Attempting to generate sum number of "x"-marked cells by department.

Previously used countifs on other excel document with formula and criteria on same sheet. Worked just fine.

TROUBLE: When changing 'reference 1' to a different sheet, activation within formula (i.e. coloring to show recognition) diminishes to black and formula fails to work.

ATTEMPTED SOLUTION: Googled issue, found this.
(Example) =COUNTIF(INDIRECT("'" & A2 & "'!$G$10:$G$203"),A12)

(Implemented 1) =countifs(indirect("'"&Q1L"'!$E$7:$H$400"),"x",ind irect("'"&Q1L"'!$Y$7:$Y$400"),O35)

None of the references activate in color with the changed formula.

Any advice from anyone?