Matching SUMIF with partial lookup

excel, excel-2010, excel-formula

Solution

`SUMIF` takes wildcards using `*`

for example this formula `=SUMIF($A$1:$A$9,"Nike*",$B$1:$B$9)` sums B1:B9 where A1:A9 start with "Nike"

snapshot of detailed example below This formula `=SUMIF($A$1:$A$9,C1&"*",$B$1:$B$9)` copied down does a SUMIF on C1:C3

Problem

I want to do a SUMIF of sorts... But I want the criteria to be a subset of the matched cell. i.e. I have customer stores: ``` Nike - Melbourne Nike - Sydney Nike - Brisbane Adidas - Melbourne Adidas - Sydney Adidas - Brisbane Reebok - Melbourne Reebok - Sydney Reebok - Brisbane ``` What I'm trying to do is sum the monthly values of all stores together - I have many stores with many locations - I want to combine all like stores into a single figure. I have created a new column with simply: ``` Nike Adidas Reebok ``` I want to a sumif and sum all the monthly values where store "CONTAINS" Nike and Store "CONTAINS" Adidas. Currently, the SUMIF matches the cell contents exactly - how can I do a partial match?

Original source