Probably not with SUMIF, but it can be done with SUMPRODUCT.



e.g.

=SUMPRODUCT(--(ISNUMBER(MATCH("*"&$C$1:$C$5&"*",A1,0))),$D$1:$D$ 5)

Where A1 is the cell containing the 5 states, and C1:C5 is the list you want to look in to find the matches and sum the adjacent cells D1: D5.

Warning... with Sumproduct it is advisable to not use full column ranges as this function is more processor intensive.