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

I think I've narrowed down the issue, and I'm trying to expand the initial criteria range across several columns. COUNTIFS appears incapable of managing several columns. I've looked into sumproduct, but don't know how to replicate the function.

Is there a way to accomplish:

=countifs(Q1L!$E$7:$H$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

from

=countifs(Q1L!$E$7:$E$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

Switched criteria 1 $E$400 to $H$400 to expand columns.

Please advise.

I think I've narrowed down the issue, and I'm trying to expand the
initial criteria range across several columns. COUNTIFS appears
incapable of managing several columns. I've looked into sumproduct,
but don't know how to replicate the function.

Is there a way to accomplish:

=countifs(Q1L!$E$7:$H$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

from

=countifs(Q1L!$E$7:$E$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

Switched criteria 1 $E$400 to $H$400 to expand columns.

Please advise.

The function works on the 'range' specified regardless of the number
rows/cols. The formula should work 'as is' as far as I can see.

I'm not familiar with SUMPRODUCT and so can't offer any advice on
whether it would be more suitable in this scenario. Sorry!

--
Garry

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

Hi Christian,

Am Thu, 14 Mar 2013 06:45:42 +0000 schrieb Christian Michael:

=countifs(Q1L!$E$7:$H$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

from

=countifs(Q1L!$E$7:$E$400,"x",Q1L!$Y$7:$Y$400,Q1M! O35)

Switched criteria 1 $E$400 to $H$400 to expand columns.

try:
=SUMPRODUCT((Q1L!$E$7:$H$400="x")*(Q1L!$Y$7:$Y$400 =Q1M!O35))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks for all your help, Garry!

Thank you, Mr. Busch! It works!

Can you tell me how this translates? I've never thought of using a multiplier "*" to add things like this? Or does it not work as that in this instance?