Counting w/ multiple criteria using named ranges

Is it possible to use an array formula with 2 named ranges?

{=sum((PROV="AB")*(CLASS=1))}
where Prov and Class are equal and are entered as such:
PROV=OFFSET(Sheet1!$G$3,0,0,COUNT(Sheet1!$G:$G),1)
CLASS=OFFSET(Sheet1!$H$3,0,0,COUNT(Sheet1!$H:$H),1 )

doesn't seem to work, but the ranges should be the same at
any given time.

TIA
Kevin M

Array formulas have trouble with entire column ranges. Untested, but try,
for example, $G1:$G65535 rather than $G:$G.

--

Vasant

"Kevin M" wrote in message
...
Is it possible to use an array formula with 2 named ranges?

{=sum((PROV="AB")*(CLASS=1))}
where Prov and Class are equal and are entered as such:
PROV=OFFSET(Sheet1!$G$3,0,0,COUNT(Sheet1!$G:$G),1)
CLASS=OFFSET(Sheet1!$H$3,0,0,COUNT(Sheet1!$H:$H),1 )

doesn't seem to work, but the ranges should be the same at
any given time.

TIA
Kevin M

Hi
try
=SUMPRODUCT((PROV="AB")*(CLASS=1))
both range have to be of the same size

--
Regards
Frank Kabel
Frankfurt, Germany


Kevin M wrote:
Is it possible to use an array formula with 2 named ranges?

{=sum((PROV="AB")*(CLASS=1))}
where Prov and Class are equal and are entered as such:
PROV=OFFSET(Sheet1!$G$3,0,0,COUNT(Sheet1!$G:$G),1)
CLASS=OFFSET(Sheet1!$H$3,0,0,COUNT(Sheet1!$H:$H),1 )

doesn't seem to work, but the ranges should be the same at
any given time.

TIA
Kevin M

Check whether re-defining PROV and CLASS gives the expected results:

PROV ==
Sheet!$G$3:INDEX(Sheet1!$G:$G,MATCH(9.999999999999 99E+307,Sheet1!$H:$H))
CLASS ==
Sheet!$H$3:INDEX(Sheet1!$H:$H,MATCH(9.999999999999 99E+307,Sheet1!$H:$H))

where the Match bit makes use of the numeric range in H in both definitions.

Now try your array formula

{=sum((PROV="AB")*(CLASS=1))}

or the equivalent

=SUMPRODUCT(--(PROV="AB"),--(CLASS=1))

which you need to confirm with just enter.

"Kevin M" wrote in message
...
Is it possible to use an array formula with 2 named ranges?

{=sum((PROV="AB")*(CLASS=1))}
where Prov and Class are equal and are entered as such:
PROV=OFFSET(Sheet1!$G$3,0,0,COUNT(Sheet1!$G:$G),1)
CLASS=OFFSET(Sheet1!$H$3,0,0,COUNT(Sheet1!$H:$H),1 )

doesn't seem to work, but the ranges should be the same at
any given time.

TIA
Kevin M