Using COUNTIFS with dynamic ranges?

[Kagemucha]

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..

[oldchippy]

Cross posting

http://www.ozgrid.com/forum/showthread.php?t=72189

[Kagemucha]

Was I not suppose to post on other excel forums as well? I'm really sorry if I broke the rules

[Kagemucha]

This problem is solved:

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

It turns out this WAS the case. Column Q has data in Row 1, whereas Column M has nothing.

However, I don't see WHY this happened.. I specifically tell my dynamic ranges to START at row 4. Why is it looking at data from Row 1?

[nattasiray]

Are there any data in the cells above Cell M4? If the answer is yes, please deduct with the number of non blank above cell.

For example All of cell M1, M2 and M3 contain Data. You should change reference of dynamic range of ResultsM from

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

to

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

From help online of COUNTIFS Function

COUNTIFS
Counts the number of cells within a range that meet multiple criteria.

Syntax

COUNTIFS(range1, criteria1,range2, criteria2…)

Range1, range2, … are 1 to 127 ranges in which to evaluate the associated criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.


I do not sure that the criteria that you assign is missing equal sign.

=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")

[VBA Noob]

Closed for cross posting. Please read forum rules. IF you agree to them then PM me and I will unlock the thread

VBA Noob