SUMPRODUCT - Looking up an array in an array?

[fun2excel4money]

Hello Friends,

[TABLE="width: 704"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64, align: right"]9[/TD]
[TD="width: 64, align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]86[/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PA[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]VT[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am attempting to use sumproduct to sum up instances of 1,2 and 4 that correspond to CA. Therefore my total should be 155 (26, 90, 56). The formula i am using right now is =SUMPRODUCT((B1:K1=A11:A13)*(A3:A7=B10)*B3:K7), where A11:A13 are the numbers "1,2,4". However, i think the issue arises in =SUMPRODUCT((B1:K1=A11:A13)*(A3:A7=B10)*B3:K7), where I am trying to look up an array in an array. Is there a work around to this so that a sumproduct can determine the answer? Thanks in advance for the help!

 

[Jonmo1]

Try

=SUMPRODUCT(SUMIF($B$1:$K$1,$A$11:$A$13,INDEX($B$3:$K$7,MATCH(B11,$A$3:$A$7,0),0)))

 

[fun2excel4money]

try

=sumproduct(sumif($b$1:$k$1,$a$11:$a$13,index($b$3:$k$7,match(b11,$a$3:$a$7,0),0)))

thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu

 

[Jonmo1]

You're welcome.