shorten multiple Index Match formula

[psrs0810]

I have this long drawn out Index Match-ing formula that I am hoping can be shortened.


=ROUND((OFFSET(INDEX(K$1:K$2094,MATCH($A2304,$A$1:$A$2094,0)),6,0)*$D2304)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2305,$A$1:$A$2094,0)),6,0)*$D2305),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2306,$A$1:$A$2094,0)),6,0)*$D2306),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2307,$A$1:$A$2094,0)),6,0)*$D2307),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2308,$A$1:$A$2094,0)),6,0)*$D2308),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2309,$A$1:$A$2094,0)),6,0)*$D2309),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2310,$A$1:$A$2094,0)),6,0)*$D2310),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2311,$A$1:$A$2094,0)),6,0)*$D2311),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2312,$A$1:$A$2094,0)),6,0)*$D2312),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2313,$A$1:$A$2094,0)),6,0)*$D2313),0)+IFERROR((OFFSET(INDEX(K$1:K$2094,MATCH($A2314,$A$1:$A$2094,0)),6,0)*$D2314),0),0)

Please let me know
thanks

 

[Comfy]

=ROUND(SUM(SUBTOTAL(6,OFFSET($A$1,MATCH($A$2304:$A$2314,$A$1:$A$2094,0)+5,10),$A$2304:$A$2314)),0)

NVM larger sample.... wrong

 

[Comfy]

The OFFSET part correctly returns the values in Column A but for some reason SUMPRODUCT can't evaluate the array????

=SUMPRODUCT(OFFSET($A$1,MATCH($A$2304:$A$2314,$A$1:$A$2094,0)+5,10),$A$2304:$A$2314) = 0

But using F9 to evaluate returns:

=SUMPRODUCT({7;8;9;10;11;12;13;14;15;16;17},{1;2;3;4;5;6;7;8;9;10;11}) = 902

 

[Comfy]

This gives the right answer for my test data:

=ROUND(SUMPRODUCT(SUBTOTAL(6,OFFSET($A$1,MATCH($A$2304:$A$2314,$A$1:$A$2094,0)+5,10)),$A$2304:$A$2314), 0)