Menu
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?
722
You can use VLOOKUP and SUMIF (or SUMIFS for multiple criteria) together in Excel for various purposes—for example: VLOOKUP within SUMIF, when you need to sum values based on conditions, but you also have to lookup from another table to get the correct criteria value.
Use of SUMIFS with INDEX & MATCH Functions in ExcelIf you use the SUMIFS function with the INDEX and MATCH functions inside, you have the ability to add more than one criterion, which you can't do by just using the SUMIF function. To do this, ensure you input your Sum Range, then Criteria Range, then Range Criteria.
Excel supports the wildcard characters "*" and "?", and these wildcards can be used to perform partial (substring) matches in various lookup formulas. However, if you use wildcards with a number, you'll convert the numeric value to a text value.
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
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
