Using COUNTIFS with dynamic ranges?

[kagemucha2]

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)

The formula is simply:

=COUNTIFS(ResultsQ,"GENDER",ResultsM,"MALE"

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")

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

 

[seenfresh]

Is your named reference in the same worksheet as your formula... you cannot use a named reference to an external ws or wb...?

 

[kagemucha2]

Yes they are. When I use my named references in COUNTIF, they work. ie:

=COUNTIF(ResultsQ, "GENDER")
=COUNTIF(ResultsM, "MALE")

So I know my named references WORK, but for some reason, COUNTIFS just doesn't recognize multiple named references?

 

[Dutchy]

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

 

[kagemucha2]

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?

 

[Dutchy]

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)