joint regions problem...

I have two vertical row ranges, and want to combine them in a formula. The old range was "data", the new is a combination of two ("Feb_11", "Jun_11") - the two regions are all rows, and contiguous, and a subset of the old "data" region.

It works fine in most formulae, but fails in a count usage; Is this just another Excel anomaly, or am I doing something wrong?

I've tried parenthesizing it in various ways, which should not be required, but still didn't help.

Thanks.

Works:
=AVERAGE(AZ:AZ ) [old]
=COUNTIF(AZ:AZ data, ">="&(AY162*(AZ:AZ Total))) [old]

=AVERAGE(AZ:AZ (Feb_11,Jun_11)) [new]

Fails: (#Value)
=COUNTIF(AZ:AZ (Feb_11,Jun_11), ">="&(AY162*(AZ:AZ Total))) [new]

If I evaluate the two ranges separately, ti works fine.
=COUNTIF(AZ:AZ Feb_11, ">="&(AY162*(AZ:AZ Total)))
+COUNTIF(AZ:AZ Jun_11, ">="&(AY162*(AZ:AZ Total)))

Oddly(?) enough, if I define a new name:
Group1 = Feb_11,Jun_11

Then all the arithmetic formluae work find on it, but still the Count fails in the same way.

COUNTIF, unlike AVERAGE, requires a single contiguous range. For this reason the aggregation of 2 separate COUNTIFs works without issue and your attempt at using both ranges in a single COUNTIF does not.

I confess I am unclear as to why you are using an Intersect approach in your references ? I suspect your AZ references are superfluous.

Do you have XL2007 or later? Countifs function will do what you need, as will sumproduct or DonkeyOte said combine 2 countif

Originally Posted by guthrie

Yes, Excel 2010. And Countif fails as above.

And yes, I did revert to multiple ones to get around this limitation.

To me this is a bug - a described and logical usage of a builtin function fail, and in a very generic and non-descriptive manner. But, it's been in Excel for decades, so ... :-)

Countif is what you used in your sample, does Countifs work?

Countifs will let you add more than one range and criteria where as countif stalls at one range and one criteria

COUNTIFS is not applicable here (it is an AND based test and does not circumvent the main issue). You should aggregate your separate COUNTIF tests as previously outlined.

Regards use of entire rows and Intersect - this makes sense - thanks for the clarification.

Can you break your code down? Whats in (AZ:AZ Feb_11) thats not in (AZ:AZ Total)? i'm guessing this is on the same sheet?

And what is this referring to? (AY162*(AZ:AZ Total) could you upload a sample......I'm a little confuesd at your syntax

@scottylad2,

Please Login or Register  to view this content.

for example uses the Intersect of Column AZ and Feb_11 Named Range.

The issue here is that OP wants to apply the same criteria to two separate ranges in a single COUNTIF - this is not possible for those reasons outlined previously.