I have a large table of data (incident reports) and I want to use an array
formula to give me a count of incidents where
- the month is January (months are in column A)
- the region is Nelson, Marlborough or Canterbury (regions are in col B)
- the body part is foot, knee or leg (body parts are in col C)

I have a named range called UpperSouth which contains Nelson, Marlborough
and Canterbury
and I have a named range called LowerLimb which contains leg, foot and knee

I can get a count of all incidents in UpperSouth area in January using the
array formula
=COUNT(IF(Data!$A$2:$A$1000="January",MATCH(Data!$ B$2:$B$1000,UpperSouth,0)))

or I can get a count of all incidents in January involving lower limbs by
using
=COUNT(IF(Data!$A$2:$A$1000="January",MATCH(Data!$ C$2:$C$1000,LowerLimb,0)))

but what I can't work out is how to nest my MATCHes so I can get a count of
incidents in January, in the UpperSouth area involving lower limbs.

Help!
Thanks
Katy

Try this normally entered formula (not array entered):

=SUMPRODUCT(--(Data!$A$2:$A$1000="January"),--(ISNUMBER(MATCH(Data!$B$2:$B$1000,UpperSouth,0))),--(ISNUMBER(MATCH(Data!$C$2:$C$1000,LowerLimb,0))))


--
Biff
Microsoft Excel MVP


"katy" wrote in message
...
I have a large table of data (incident reports) and I want to use an array
formula to give me a count of incidents where
- the month is January (months are in column A)
- the region is Nelson, Marlborough or Canterbury (regions are in col B)
- the body part is foot, knee or leg (body parts are in col C)

I have a named range called UpperSouth which contains Nelson, Marlborough
and Canterbury
and I have a named range called LowerLimb which contains leg, foot and
knee

I can get a count of all incidents in UpperSouth area in January using the
array formula
=COUNT(IF(Data!$A$2:$A$1000="January",MATCH(Data!$ B$2:$B$1000,UpperSouth,0)))

or I can get a count of all incidents in January involving lower limbs by
using
=COUNT(IF(Data!$A$2:$A$1000="January",MATCH(Data!$ C$2:$C$1000,LowerLimb,0)))

but what I can't work out is how to nest my MATCHes so I can get a count
of
incidents in January, in the UpperSouth area involving lower limbs.

Help!
Thanks
Katy

Great! that works a treat.
I assume the benefit of doing a sumproduct, rather than an array formula, is
that it's quicker and smaller (in terms of file size)?
And if I wanted to exclude one of the named ranges, for example if I want a
count of all incidents OUTSIDE the UpperSouth region, I would just replace
the ISNUMBER with ISNA in the relevant part of the formula?

Thank you so much for you help.
Katy

"T. Valko" wrote:

Try this normally entered formula (not array entered):

=SUMPRODUCT(--(Data!$A$2:$A$1000="January"),--(ISNUMBER(MATCH(Data!$B$2:$B$1000,UpperSouth,0))),--(ISNUMBER(MATCH(Data!$C$2:$C$1000,LowerLimb,0))))


--
Biff
Microsoft Excel MVP


"katy" wrote in message
...
I have a large table of data (incident reports) and I want to use an array
formula to give me a count of incidents where
- the month is January (months are in column A)
- the region is Nelson, Marlborough or Canterbury (regions are in col B)
- the body part is foot, knee or leg (body parts are in col C)

I have a named range called UpperSouth which contains Nelson, Marlborough
and Canterbury
and I have a named range called LowerLimb which contains leg, foot and
knee

I can get a count of all incidents in UpperSouth area in January using the
array formula
=COUNT(IF(Data!$A$2:$A$1000="January",MATCH(Data!$ B$2:$B$1000,UpperSouth,0)))

or I can get a count of all incidents in January involving lower limbs by
using
=COUNT(IF(Data!$A$2:$A$1000="January",MATCH(Data!$ C$2:$C$1000,LowerLimb,0)))

but what I can't work out is how to nest my MATCHes so I can get a count
of
incidents in January, in the UpperSouth area involving lower limbs.

Help!
Thanks
Katy