sum based on PARTIAL content of another cell

[mmartinucla]

I have a worksheet where the first column contains a special code
specific to each product we sell, containing a code for 1) the
territory where it's sold; 2) the vendor; 3) the product name; etc. I
am trying to figure out a way to create a sumif formula (or other
formula if necessary) to only sum, for example, values where the
vendor is XYZ. Here's an example:

A B

1 C-SRS-TMN-ARBYS-5 50
2 C-SRS-SLF-TESTY-1 75
3 C-SRS-INC-CRASHY-3 100
4 C-SRS-TMN-TESTY-3 50
5 C-SRS-TMN-TESTY-4 80

So, let's say I want to sum everything that has a Vendor of "TMN" (the
third set within the code). The result should be 180 (B1+B4+B5). Or,
if I want to sum everything has a title of "Testy" (the fourth set
within the code). The result of that should be 205 (B2+B4+B5). Or,
even more complex, anything sold by TMN with the Title of "Testy" (130
= B4+B5).

I designed this whole coding system thinking I could do a combination
of SUMIF and the FIND/LEFT/MID functions, but realized after
implementing it that that just wouldn't work. Any help would be
GREATLY appreciated!

Thanks!

 

[Peo Sjoblom]

Use wildcards

=SUMIF(A1:A5,"*-TMN-*",B1:B5)

 

[T. Valko]

Your posted samples all begin with "C-SRS". If that's how it is in the real
situation then you can probably use Peo's suggestion. If, however, you have
a lot of different combinations of codes in each position then it could get
really complicated.

An easy way to handle this would be to split the code into its individual
segments into individual cells.

So, to sum for "A", "XXX", "TMN", "TESTY":

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="XXX"),--(C1:C10="TMN"),--(D110="TESTY"),E1:E10)

Or, better to use cells to hold the criteria:

J1 = A
J2 = XXX
J3 = TMN
J4 = TESTY

=SUMPRODUCT(--(A1:A10=J1),--(B1:B10=J2),--(C1:C10=J3),--(D110=J4),E1:E10)

Biff

 

[mmartinucla]

Your posted samples all begin with "C-SRS". If that's how it is in the real
situation then you can probably use Peo's suggestion. If, however, you have
a lot of different combinations of codes in each position then it could get
really complicated.

An easy way to handle this would be to split the code into its individual
segments into individual cells.

So, tosumfor "A", "XXX", "TMN", "TESTY":

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="XXX"),--(C1:C10="TMN"),--(D110="TESTY"),E1:E10)

Or, better to use cells to hold the criteria:

J1 = A
J2 = XXX
J3 = TMN
J4 = TESTY

=SUMPRODUCT(--(A1:A10=J1),--(B1:B10=J2),--(C1:C10=J3),--(D110=J4),E1:E10)

Biff

I was thinking of breaking it up into its individual cells, but so
far, Peo's suggestion seems to be working perfectly. Thanks for all
the help!

 

[mmartinucla]

Use wildcards

=SUMIF(A1:A5,"*-TMN-*",B1:B5)

So much less complicated than I'd made it in my head! Thanks!!