sum based on PARTIAL content of another cell
 

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!

sum based on PARTIAL content of another cell
 

Use wildcards

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


--
Regards,

Peo Sjoblom


wrote in message
ups.com...
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!

sum based on PARTIAL content of another cell
 

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"),--(D1:D10="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),--(D1:D10=J4),E1:E10)

Biff

wrote in message
ups.com...
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!

sum based on PARTIAL content of another cell
 

On Apr 5, 7:04 pm, "T. Valko" wrote:
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"),--(D1:D10="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),--(D1:D10=J4),E1:E10)

Biff

wrote in message

ups.com...

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 onlysum, 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 tosumeverything that has a Vendor of "TMN" (the
third set within the code). The result should be 180 (B1+B4+B5). Or,
if I want tosumeverything 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!

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!

sum based on PARTIAL content of another cell
 

On Apr 5, 6:30 pm, "Peo Sjoblom" wrote:
Use wildcards

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

--
Regards,

Peo Sjoblom

wrote in message

ups.com...

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 onlysum, 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 tosumeverything that has a Vendor of "TMN" (the
third set within the code). The result should be 180 (B1+B4+B5). Or,
if I want tosumeverything 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!

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