CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
php-b-grader
php-b-grader

Reputation: 3315

Matching SUMIF with partial lookup

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?

Upvotes: 6

Views: 44305

Answers (2)

efr0702
efr0702

Reputation: 13

I had a similar need to summarize data using a partial match. The sumif formula posted worked but I tweaked it for my needs. I used countif instead of sumif.

Here's the formula: COUNTIF('Sorted'!$D$2:$D$3831, a6 & "*") Cell A6 had the partial text of interest and Column D had the entries I wanted to summarize by year. In this case, this column had the full month, day and time stamp. The wild card worked to partially match only on the year. From that I was able to summarize each year of interest (in cells a6, a7, etc.).

Upvotes: 1

brettdj
brettdj

Reputation: 55672

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

example

Upvotes: 14

Related Questions