Counting w/ multiple criteria using named ranges

  • Thread starter Thread starter Kevin M
  • Start date Start date
K

Kevin M

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.
 
Check whether re-defining PROV and CLASS gives the expected results:

PROV ==>
Sheet!$G$3:INDEX(Sheet1!$G:$G,MATCH(9.99999999999999E+307,Sheet1!$H:$H))
CLASS ==>
Sheet!$H$3:INDEX(Sheet1!$H:$H,MATCH(9.99999999999999E+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.
 
Back
Top