Excel 2007 countifs function problem

[rcindyj01]

I'm working with a large database and trying to write a countifs function.
Each part of the function works independently and some of them work together,
but when I try to add them all together, I get a #value error. I've given
each column in my database a range name to make copying functions easier.

This function works and gives a correct answer:
=COUNTIFS(Pre_DS,">0.99",Pre_DS,"<2")

This function works and gives a correct answer:
=COUNTIFS(Tx_Type,"cha",Pre_Cyl,"<-1.99",POM_3_UCVA,20)

This function gives me a #value error
=COUNTIFS(Tx_Type,"cha",Pre_Cyl,"<-1.99",POM_3_UCVA,20,Pre_DS,">0.99",Pre_DS,"<2")

 

[Bob Umlas]

your ranges aren't ALL the same size. For example, the # of rows in Pre_DS
is likely not the same as the # of rows in Tx_Type.

 

[Roger Govier]

Following on from Bob's advice, i have written a small macro that will
create a series of Dynamic named ranges for all titled columns in a
workbook, where the length of each range will be the same, and based upon
the count of values from the column which always contains an entry in each
row.

You can download this from
http://www.contextures.com/xlNames03.html