Using COUNTIFS with dynamic ranges?

K

kagemucha2

New Member
Joined
Jun 30, 2007
Messages
3
Does COUNTIFS not work with named ranges/dynamic ranges? Everytime I try, I get a #VALUE! error.

These are my 2 dynamic ranges:

ResultsM=OFFSET([results_list.xls]Results!$M$4,0,0,COUNTA([results_list.xls]Results!$M:$M),1)

ResultsQ=OFFSET([results_list.xls]Results!$Q$4,0,0,COUNTA([results_list.xls]Results!$Q:$Q),1)
Click to expand...

The formula is simply:
=COUNTIFS(ResultsQ,"GENDER",ResultsM,"MALE"
Click to expand...

However, this does NOT work. it returns the #VALUE! error. However, odly enough, when I use column references, it works:

=COUNTIFS([results_list.xls]Results!Q:Q, "GENDER", [results_list.xls]Results!M:M, "MALE")
Click to expand...

This makes me think that COUNTIFS just don't work with named references..
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Is your named reference in the same worksheet as your formula... you cannot use a named reference to an external ws or wb...?
 
Upvote 0
Yes they are. When I use my named references in COUNTIF, they work. ie:

=COUNTIF(ResultsQ, "GENDER")
=COUNTIF(ResultsM, "MALE")
Click to expand...

So I know my named references WORK, but for some reason, COUNTIFS just doesn't recognize multiple named references?
 
Upvote 0
I don't have Excel 2007 so just a guess.....

Are your named ranges the same size?, the way you have defined them leaves open the possibility that they are not
 
Upvote 0
Thank you Dutchy!!, that fixed it. Column Q has data in Row 1, whereas Column M has nothing.


However, I don't see WHY that happened.. I specifically tell my dynamic ranges to START at row 4. Why is it looking at data from Row 1?
 
Upvote 0
Your offset formula has the range starting at Q4 but the height of the range is determined by COUNTA(Q:Q) which will take account of data in all rows.

Perhaps you should change to =COUNTA(Q4:Q1048576)
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Curious Filter-by-formula issue
Replies
1
Views
84
gordonpsmith
G
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
196
Ramses505
R
K
  • Question Question
Get Data form closed Excel workbook with ADODB
Replies
2
Views
371
Kappes105
K
J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
690
juliecooper255
J
jski21
  • Question Question
Reference Cell Contents in Range Formula
Replies
8
Views
417
jski21
jski21

Forum statistics

Threads
1,224,542
Messages
6,179,424
Members
452,914
Latest member
echoix

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top