count(if(... using array formula: can I use a named range in my ca
 

I'm using an array formula to give a count of rows from a big data table. I
want a count of all records which are in a certain month, and in a certain
region.

In the Data sheet Col A contains the month and Col C is the region. In
another sheet, I have a result table with this array formula:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2 :$C$1000="Canterbury",Data!$A$2:$A$1000)))
and it works fine; I get a count of all the January records from the
Canterbury region.

But, how can I improve my array formula so that I get multiple regions in
one count? i.e. I want a count of all the January records from the
Canterbury, Nelson and Marlborough regions.

I can make my formula work if I change it to:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2 :$C$1000="Canterbury",Data!$A$2:$A$1000)))+count(i f(Data!$A$2:$A$1000="January",if(Data!$C$2:$C$1000 ="Marlborough",Data!$A$2:$A$1000)))+count(if(Data! $A$2:$A$1000="January",if(Data!$C$2:$C$1000="Nelso n",Data!$A$2:$A$1000)))

but this is horribly long and unwieldy and gets out of hand when I want to
group together more regions!

I've tried doing this, but it didn't work (it counted ALL the rows in the
data table):
=count(if(Data!$A$2:$A$1000="January",if(OR(Data!$ C$2:$C$1000="Canterbury",Data!$C$2:$C$1000="Marlbo rough",Data!$C$2:$C$1000="Nelson"),Data!$A$2:$A$10 00)))

I also thought I might be able to do it using a named range, but this didn't
work either. I created a range called UpperSouth which contained the values
Canterbury, Marlborough and Nelson, entered the text UpperSouth into cell B1,
and then I changed my array formula to:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2 :$C$1000=INDIRECT(B1),Data!$A$2:$A$1000)))
but this didn't work.

Can anyone help me with a way to incorporate different regions into one count?
(PS I know a Pivot Table would be great, but the problem's actually a bit
more complicated than I've described and I'd rather do it by an array formula
if I can!)

Thanks

count(if(... using array formula: can I use a named range in my ca
 

I worked it out! Will leave the answer here in case it's useful to others.
The formula that works is:

=count(if(Data!$A$2:$A$1000="January",Match(Data!$ C$2:$C$1000,UpperSouth,0)))



"katy" wrote:

I'm using an array formula to give a count of rows from a big data table. I
want a count of all records which are in a certain month, and in a certain
region.

In the Data sheet Col A contains the month and Col C is the region. In
another sheet, I have a result table with this array formula:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2 :$C$1000="Canterbury",Data!$A$2:$A$1000)))
and it works fine; I get a count of all the January records from the
Canterbury region.

But, how can I improve my array formula so that I get multiple regions in
one count? i.e. I want a count of all the January records from the
Canterbury, Nelson and Marlborough regions.

I can make my formula work if I change it to:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2 :$C$1000="Canterbury",Data!$A$2:$A$1000)))+count(i f(Data!$A$2:$A$1000="January",if(Data!$C$2:$C$1000 ="Marlborough",Data!$A$2:$A$1000)))+count(if(Data! $A$2:$A$1000="January",if(Data!$C$2:$C$1000="Nelso n",Data!$A$2:$A$1000)))

but this is horribly long and unwieldy and gets out of hand when I want to
group together more regions!

I've tried doing this, but it didn't work (it counted ALL the rows in the
data table):
=count(if(Data!$A$2:$A$1000="January",if(OR(Data!$ C$2:$C$1000="Canterbury",Data!$C$2:$C$1000="Marlbo rough",Data!$C$2:$C$1000="Nelson"),Data!$A$2:$A$10 00)))

I also thought I might be able to do it using a named range, but this didn't
work either. I created a range called UpperSouth which contained the values
Canterbury, Marlborough and Nelson, entered the text UpperSouth into cell B1,
and then I changed my array formula to:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2 :$C$1000=INDIRECT(B1),Data!$A$2:$A$1000)))
but this didn't work.

Can anyone help me with a way to incorporate different regions into one count?
(PS I know a Pivot Table would be great, but the problem's actually a bit
more complicated than I've described and I'd rather do it by an array formula
if I can!)

Thanks