IF(AND) in an array formula

[Babymech]

I think I'm having problems using IF(AND in an array formula. I've put
together the following formula, which I've gotten help with already from this
discussion group:

{=INDEX(Country;MEDIAN(IF(Owner<>"";IF(Category<=9;MATCH(Country;Country;0)))))}

With my three named ranges, Country, Owner, and Category, I check to see the
following: For all instances where the owner cell isn't blank, and the
category value is less than or equal to 9, I want to see what the most
frequent Country string is - if there are more German owners than Spanish,
for example.

This works fine, and I'm pleased with it. The problem comes in when I want
to include all Categories between two numbers. What I assumed would work is
the following:

{=INDEX(Country;MEDIAN(IF(Owner<>"";IF(AND(Category>=3;Category<=9);MATCH(Country;Country;0)))))}

The result I get here is #NUM. As far as I can tell, the only thing I'm
doing is substituting the condition that Category is less than a number, with
the condition that Category is between two numbers, which to me seems like a
perfectly reasonable thing to look for in an array... All help is appreciated
as always.

 

[Bob Phillips]

=INDEX(Country;MEDIAN(IF(Owner<>"";IF(Category>=3;IF(Category<=9;MATCH(Country;Country;0))))))

 

[Babymech]

Perfect, thanks. I didn't even think of piling two IFs on each other, even
though I'd already done it with Owner. Now it works fine.

 

[Bob Phillips]

You can AND it by using *

=INDEX(Country;MEDIAN(IF(Owner<>"";IF((Category>=3)*(Category<=9);MATCH(Country;Country;0)))))

or even go the whole hog with


=INDEX(Country;MEDIAN(IF((Owner<>"")*(Category>=3)*(Category<=9);MATCH(Country;Country;0))))