Geek asks and answers

SUMIF partial search matching

I want to make a SUMIF sort ... But I want the criteria to be a subset of the corresponding cell.

i.e. I have stores for customers:

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 to summarize the monthly values ​​of all stores together - I have many stores with many addresses - I want to combine all such stores into one digit.

I created a new column simply:

Nike
Adidas
Reebok

I want to get sumif and summarize all the monthly values ​​in which the store "CONTAINS" Nike and Store "CONTAINS" Adidas.

SUMIF currently exactly matches the contents of the cell - how can I do a partial match?

+5
source share
2 answers

SUMIF takes wildcards with *

, =SUMIF($A$1:$A$9,"Nike*",$B$1:$B$9)
B1: B9, A1: A9 "Nike"

=SUMIF($A$1:$A$9,C1&"*",$B$1:$B$9)
, SUMIF C1: C3

example

+12

, . sumif , . countif sumif.

: COUNTIF ('Sorted'! $D $2: $D $3831, a6 "*" ) A6 , D , . , . , . ( a6, a7 ..).

+1
source

More articles:


All Articles