Sum If conditin based on Array criteria

I have an formula i did two different ways that ads up amounts in a column an item in an array matches an item in the match column.

array formula
{=SUM(IF( ( {"2","b3","a23","3"}=TEXT(C11:C19,"#") ),D11:D19, 0))}

standard sumproduct
=SUMPRODUCT(({"2","b3","a23","3"}=TEXT($C$12:$C$19,"#"))*--($D$12:$D$19))

However I would like to take the array part {"2","b3","a23","3"} out and reference the array as a value in a cell B2. So that Cell B2 had either {"2","b3","a23","3"} or "2","b3","a23","3"


=SUM(IF( ( INDEX( B2,0,0)=TEXT(C11:C19,"#") ),D11:D19, 0))
=SUMPRODUCT((INDEX(B2,0,0) =TEXT($C$12:$C$19,"#"))*--($D$12:$D$19))

However those formulas don't work.

I have successfully used the index function in the past, but this did not work. Using the Evaluate Formula option I see it added Extra quotes around every item including the braces "{" and "}". Index by itself does not add the extra quotes.

Is there a trick as I want to use variations of this formula several times and want just one array reference.

any an all thoughts are appreciated.

thanks

Alan

Thanks for the fast reply.

I was doing TEXT($C$12:$C$19,"#") to force the criteria range to be text so that "2" would equal to "2" and not 2. I tried things both ways and this seemed cleaner at the time. That part of my process seemed good.

I would prefer the range to be in an array with braces in a single cell like {a, b, c} and not having a, b, c in different cells as the number of critera for my various sums will change so i might have 3 items to 10 items so doing the single array seemed "safer".

Here is a link to a sample file of what I was trying
https://www.dropbox.com/s/ukim6y8q1z...arry.xlsx?dl=0

Tony,

Wow I did not realize the formula would be so crazy. It seems like if index(b2,0,0) returns as nice array {....} that it should do it inside the other formulas instead of returning everything including the quotes in braces. The sheet I had was a sample test sheet. and the criteria range was going to be on another sheet. I guess I will rework it more to use your nice clean formula and have all the codes in their own cells. I am assuming that having extra blank cells referenced in the criteria won't break anything i.e. referencing F2:z2 instead of F2:i2 =SUMPRODUCT(SUMIF(C$12:C$20,F2:z2,D$12:D$20)


I really appreciate your response to this. Just wish index inside the others acted differently. I have used Index for other things and had what needed and liked it.

thanks

Alan