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

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.

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

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!

In article .com,
wrote:

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.

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

Try

=SUMPRODUCT(--ISNUMBER(MATCH(Range1,{1,2,6,12},0)),--ISNUMBER(MATCH(Range2,{1,2},0)))

change range accordingly


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


wrote in message
oups.com...
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.

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

....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.