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?

Christian Michael wrote :
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?

You need to prepend the ref cell addresses with the sheetname where
those refs point. So on "Sheet1"...

='Sheet2'!$A$1

...points to A1 on "Sheet2"!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks for your input! But I'm still having issues.

UPDATED FORMULA:
=COUNTIFS('Q1L'!$E$7:$H$400,"x",'Q1L'!$Y$7:$Y$400, Q1M!O35)

Upon hitting Tab, it force changes the formula back to:
=COUNTIFS(Q1L!$E$7:$H$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

Unless I misunderstood what your instructions meant?

Thanks for your input! But I'm still having issues.

UPDATED FORMULA:
=COUNTIFS('Q1L'!$E$7:$H$400,"x",'Q1L'!$Y$7:$Y$400, Q1M!O35)

Upon hitting Tab, it force changes the formula back to:
=COUNTIFS(Q1L!$E$7:$H$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

Unless I misunderstood what your instructions meant?

No, you got it right! Why I include the apostrophes is in case
sheetnames have spaces. Excel will remove the apostrophes otherwise.
(IOW, it's not necessary to use the apostrophes if sheetnames have no
spaces)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I should have added in my last post that in addition to the forced change, it still refuses to work.

I should have added in my last post that in addition to the forced
change, it still refuses to work.

It might be a good idea to 'study' the help file example on this
function so you have a good understanding of how to structure its
parameters. <HINTPractice using it with 1 criteria at a time!

If you still have a problem with getting it to work then post back for
further assistance.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion