I am trying to use something like the following:

a.
=SUMPRODUCT((AF3:AF465={1,2,6,12})*(AT3:AT 465={1,2}))

which returns #N/A

It works with this one:

b.
=SUMPRODUCT((AF3:AF465={1,2,6,12})*(AT3: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(AF3:AF465,{1,2,6,12},0)),--ISNUMBER(MATC
H(AT3:AT465,{1,2},0)))

Hope this helps!

In article .comgt;,wrote:

gt; I am trying to use something like the following:
gt;
gt; a.
gt; =SUMPRODUCT((AF3:AF465={1,2,6,12})*(AT3:AT 465={1,2}))
gt;
gt; which returns #N/A
gt;
gt; It works with this one:
gt;
gt; b.
gt; =SUMPRODUCT((AF3:AF465={1,2,6,12})*(AT3:AT 465={1}))
gt;
gt; .. but of course will not give me the same # since it's not counting 2s
gt; in AT.
gt;
gt; So sumproduct does not seem to like dealing with more than one array
gt; enclosed in {} .. at least the way I've tried it.
gt;
gt; I know I can use *((AT3:AT465=1) (AT3:AT465=2)) but I would like to
gt; know if there's a way to do it in the style of formula a. above., or
gt; other style that's shorter than AT3:AT465 =1 AT3:AT465=2 etc..
gt;
gt; thanks
gt; Dave R.

Try

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

change range accordingly--

Regards,

Peo Sjoblom

nwexcelsolutions.comgt; wrote in message oups.com...
gt;I am trying to use something like the following:
gt;
gt; a.
gt; =SUMPRODUCT((AF3:AF465={1,2,6,12})*(AT3:AT 465={1,2}))
gt;
gt; which returns #N/A
gt;
gt; It works with this one:
gt;
gt; b.
gt; =SUMPRODUCT((AF3:AF465={1,2,6,12})*(AT3:AT 465={1}))
gt;
gt; .. but of course will not give me the same # since it's not counting 2s
gt; in AT.
gt;
gt; So sumproduct does not seem to like dealing with more than one array
gt; enclosed in {} .. at least the way I've tried it.
gt;
gt; I know I can use *((AT3:AT465=1) (AT3:AT465=2)) but I would like to
gt; know if there's a way to do it in the style of formula a. above., or
gt; other style that's shorter than AT3:AT465 =1 AT3:AT465=2 etc..
gt;
gt; thanks
gt; Dave R.
gt;
....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.