Can sumproduct do two {x,y,z} arguments?

  • Thread starter Thread starter ucdcrush
  • Start date Start date
U

ucdcrush

I am trying to use something like the following:

a.
=SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$465={1,2}))

which returns #N/A

It works with this one:

b.
=SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$465={1}))

... but of course will not give me the same # since it's not counting 2s
in AT.

So sumproduct does not seem to like dealing with more than one array
enclosed in {} .. at least the way I've tried it.

I know I can use *((AT3:AT465=1) + (AT3:AT465=2)) but I would like to
know if there's a way to do it in the style of formula a. above., or
other style that's shorter than AT3:AT465 =1 + AT3:AT465=2 + etc..

thanks
Dave R.
 
Try...

=SUMPRODUCT(--ISNUMBER(MATCH($AF$3:$AF$465,{1,2,6,12},0)),--ISNUMBER(MATC
H($AT$3:$AT$465,{1,2},0)))

Hope this helps!
 
....or the array formula
=SUMPRODUCT(MMULT(--(range1=bin1),TRANSPOSE(COLUMN(bin1)^0)),
MMULT(--(range2=bin2),TRANSPOSE(COLUMN(bin2)^0)))
bin1={1,2,6,12}
bin2={1,2}
....with apologies to Harlan Grove.
 
Back
Top