Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Location: On the Road
Posts: 11
Default COUNTIFS ref other sheet, common solution fail

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default COUNTIFS ref other sheet, common solution fail

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


  #3   Report Post  
Junior Member
 
Location: On the Road
Posts: 11
Default

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?

Last edited by Christian Michael : March 11th 13 at 06:14 AM Reason: Verb correction
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default COUNTIFS ref other sheet, common solution fail

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


  #5   Report Post  
Junior Member
 
Location: On the Road
Posts: 11
Default

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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default COUNTIFS ref other sheet, common solution fail

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


  #7   Report Post  
Junior Member
 
Location: On the Road
Posts: 11
Default

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.
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default COUNTIFS ref other sheet, common solution fail

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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default COUNTIFS ref other sheet, common solution fail

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
  #10   Report Post  
Junior Member
 
Location: On the Road
Posts: 11
Default

Thanks for all your help, Garry!

Quote:
Originally Posted by GS[_2_] View Post
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


  #11   Report Post  
Junior Member
 
Location: On the Road
Posts: 11
Default

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?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countifs in Excel 2000? Alternative solution sought!!! Lisa Excel Discussion (Misc queries) 3 March 30th 10 12:20 AM
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr Steve Excel Worksheet Functions 2 January 4th 09 06:36 PM
Data from diff sheets to one common sheet Ross Excel Worksheet Functions 4 August 7th 07 01:19 AM
Need Spread Sheet Solution Help [email protected] Excel Discussion (Misc queries) 3 May 1st 07 06:07 AM
inserting rows at common word in a sheet Ankur Excel Discussion (Misc queries) 0 August 10th 05 11:18 AM


All times are GMT +1. The time now is 04:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"