Help with SUMIF function with CONTAINS functionality

Suppose I have the following table:

  State        CompanyTypes     Year      Sales
  AZ           A, C             2008      
  CA           B, C, D          2009      
  TX           C                2007      
  WA           A, D             2008      

I need to fill out the 开发者_JAVA百科Sales column, based on a SUM of sales across all company types for some particular state in some particular year.

The source table with all the data looks like this:

 Year    State     CompanyType   TotalSales  
 2008    AZ        A             100
 2008    AZ        C             500
 2009    CA        B             9000
 2009    CA        C             15664
 2009    CA        D             12351
 2008    TX        C             5
 2009    WA        A             789

I am familiar with SUMIF, but I do not know how to fill in the Sales column of the first table because of the comma separated values in the CompanyTypes column.

Does anybody know??

---

Ray, I notice that the sales table has the following property: In each row, the state and year combination is unique in the sense that "AZ" and "2008" appear only once amongst all the rows. If this holds true, then this formula should work

=SUMIFS(D4:D10,A4:A10,I4,B4:B10,G4)

This formula finds the sum of all sales of a particular state in a particular year, irrespective of the company. Note that this won't work in case you have something like this in the sales table.

AZ  A  2008
AZ  C  2008

Instead, you can use the following formula which is 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 this directly into the formula bar. This formula contains 4 'IF' blocks, one for each type of company. If there are more, the corresponding 'IF' blocks should also be added.

Google Docs link containing the two tables. The formula has been tested in Excel 2010. Google Docs doesn't show the formula, but the sum which it calculated when importing the excel file.

Hope this helps.

---

Based on the exact format in CompanyTypes of "A, B, C" (i.e. spaces matter), you can use the following formula. I used the Named Range of "CT" for CompanyType, "TS" for TotalSales, "State" for State, all in the Source Table. I had the CompanyTypes in column B and the first State column is A.

=SUMIFS(TS, CT, MID(B2,1,1), State, A2) + 
 SUMIFS(TS, CT, MID(B2,4,1), State, A2) +
 SUMIFS(TS, CT, MID(B2,7,1), State, A2)

Just put this in the first Sales cell (assumed row 2 for example) and fill down.