excel - Help with SUMIF function with CONTAINS functionality -

suppose have following table:

  state        companytypes     year      sales   az           a, c             2008         ca           b, c, d          2009         tx           c                2007         wa           a, d             2008       

i need fill out sales column, based on sum of sales across company types particular state in particular year.

the source table data looks this:

 year    state     companytype   totalsales    2008    az                    100  2008    az        c             500  2009    ca        b             9000  2009    ca        c             15664  2009    ca        d             12351  2008    tx        c             5  2009    wa                    789 

i familiar sumif, not know how fill in sales column of first table because of comma separated values in companytypes column.

does know??

ray, notice sales table has following property: in each row, state , year combination unique in sense "az" , "2008" appear once amongst rows. if holds true, formula should work

=sumifs(d4:d10,a4:a10,i4,b4:b10,g4) 

this formula finds sum of sales of particular state in particular year, irrespective of company. note won't work in case have in sales table.

az   2008 az  c  2008 

instead, can use following formula lengthy though more general.

=if(     iferror(         find("a",$h4),         0     ),     sumifs(         d$4:d$10,         c$4:c$10,"a",         a$4:a$10,$i4,         b$4:b$10,$g4     ),     0 ) +if(     iferror(         find("b",$h4),         0     ),     sumifs(         d$4:d$10,         c$4:c$10,"b",         a$4:a$10,$i4,         b$4:b$10,$g4     ),     0 ) +if(     iferror(         find("c",$h4),         0     ),     sumifs(         d$4:d$10,         c$4:c$10,"c",         a$4:a$10,$i4,         b$4:b$10,$g4     ),     0 ) +if(     iferror(         find("d",$h4),         0     ),     sumifs(         d$4:d$10,         c$4:c$10,"d",         a$4:a$10,$i4,         b$4:b$10,$g4     ),     0 ) 

you can copy-paste directly formula bar. formula contains 4 'if' blocks, 1 each type of company. if there more, corresponding 'if' blocks should added.

google docs link containing 2 tables. formula has been tested in excel 2010. google docs doesn't show formula, sum calculated when importing excel file.

hope helps.