SUMPRODUCT with "Contains" criteria
 

Good afternoon,
Is there any way in which SumProduct can be used with a "contains" criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

SUMPRODUCT with "Contains" criteria
 

One way

In C1:
=SUMPRODUCT(--ISNUMBER(SEARCH("1A",A1:A100)),B1:B100)

Replace SEARCH with FIND if you need it to be a stricter case sensitive
search. SEARCH is not case sensitive.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pete Rooney" wrote:
Good afternoon,
Is there any way in which SumProduct can be used with a "contains" criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

SUMPRODUCT with "Contains" criteria
 

=SUMPRODUCT(--(NOT(ISERROR(FIND("1A",A1:A100)))),B1:B100)

Regards,
Stefi

€žPete Rooney€� ezt Ã*rta:

Good afternoon,
Is there any way in which SumProduct can be used with a "contains" criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

SUMPRODUCT with "Contains" criteria
 

Try something like this:

With D1 containing the ProdCode segment to find (eg 1A)

This formula returns the sum of sales for ProdCodes containing that code:
E1: =SUMIF(A1:A100,"*"&D1&"*",B1:B100)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Pete Rooney" wrote in message
...
Good afternoon,
Is there any way in which SumProduct can be used with a "contains"
criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears
in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

SUMPRODUCT with "Contains" criteria
 

Max,

Awesome - Singapore to Cheshire UK in less than 5 minutes.

Thank you very much! :-)

Pete

"Max" wrote:

One way

In C1:
=SUMPRODUCT(--ISNUMBER(SEARCH("1A",A1:A100)),B1:B100)

Replace SEARCH with FIND if you need it to be a stricter case sensitive
search. SEARCH is not case sensitive.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pete Rooney" wrote:
Good afternoon,
Is there any way in which SumProduct can be used with a "contains" criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

SUMPRODUCT with "Contains" criteria
 

Ron,

This works fine, too!

Thank you very much. :-)

Pete

"Ron Coderre" wrote:

Try something like this:

With D1 containing the ProdCode segment to find (eg 1A)

This formula returns the sum of sales for ProdCodes containing that code:
E1: =SUMIF(A1:A100,"*"&D1&"*",B1:B100)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Pete Rooney" wrote in message
...
Good afternoon,
Is there any way in which SumProduct can be used with a "contains"
criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears
in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

SUMPRODUCT with "Contains" criteria
 

welcome, Pete.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pete Rooney" wrote in message
...
Max,

Awesome - Singapore to Cheshire UK in less than 5 minutes.

Thank you very much! :-)

Pete

SUMPRODUCT with "Contains" criteria
 

Hi, Stefi,

This works too, as long as you want "1A" only and not "1A" OR "1a".

Thanks very much! :-)

Pete



"Stefi" wrote:

=SUMPRODUCT(--(NOT(ISERROR(FIND("1A",A1:A100)))),B1:B100)

Regards,
Stefi

€žPete Rooney€� ezt Ã*rta:

Good afternoon,
Is there any way in which SumProduct can be used with a "contains" criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

SUMPRODUCT with "Contains" criteria
 

If you change Find to Search, it will treat 1A and 1a the same.

Pete Rooney wrote:

Hi, Stefi,

This works too, as long as you want "1A" only and not "1A" OR "1a".

Thanks very much! :-)

Pete

"Stefi" wrote:

=SUMPRODUCT(--(NOT(ISERROR(FIND("1A",A1:A100)))),B1:B100)

Regards,
Stefi

€žPete Rooney€� ezt Ã*rta:

Good afternoon,
Is there any way in which SumProduct can be used with a "contains" criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete

--

Dave Peterson

SUMPRODUCT with "Contains" criteria
 

I'm glad I could help......Thanks for the feedback.

***********
Regards,
Ron

XL2003, WinXP


"Pete Rooney" wrote:

Ron,

This works fine, too!

Thank you very much. :-)

Pete

"Ron Coderre" wrote:

Try something like this:

With D1 containing the ProdCode segment to find (eg 1A)

This formula returns the sum of sales for ProdCodes containing that code:
E1: =SUMIF(A1:A100,"*"&D1&"*",B1:B100)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Pete Rooney" wrote in message
...
Good afternoon,
Is there any way in which SumProduct can be used with a "contains"
criteria
- for instance, A1:100 contains product codes (for example) AZ1A1100,
ABCZ1A1200, 1A1300 and B1:B100 contains total sales for these products. I
want to extract values from column B for (for example) all products that
CONTAIN the string "1A".
I realise that I could use LEFT, RIGHT or MID as long as the "1A" appears
in
the same place in the string, but I want something to cater for the times
when it doesn't.
Thanks in advance
Pete