Using text string in SUMPRODUCT 2

[NumberCrunchingMonky]

I'm trying to configure SUMPRODUCT to use a part of a text string as my criteria.

For example:

A B C
1 Code State Total
2 123_ABC_456 NY 50
3 123_GHI-456 NY 50
4 234_ABC_567 NY 50
5 234_GHI_567 NY 50

I want to sum all the codes that contain "ABC". Unfortunately, SUMPRODUCT won't allow it conventionally, in the form: =SUMPRODUCT((Code="ABC")*(Total))

I tried to nest MID and pull out that part of the string, but I couldn't get it to work. I then tried SUMIF and couldn't get that work either.

Any thoughts?

Thanks.

NCM

 

[SkipVought]

Hi,

Would this work entered as an Array formula
[tt]
=SUM(IF(ISERROR(FIND("ABC",Code)),0,Total))
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi NumberCrunchingMonky,

I just tried this with success ..

[blue]=SUMPRODUCT((MID(Code,5,3)="ABC")*(Total))[/blue]

where Code is A2:A5, and Total is C2:C5

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[NumberCrunchingMonky]

Outstanding. Fellas--thanks.
Tony--Your formula works perfectly in this project.
Skip--Your formula fits great in another report I'm creating(and probably 10 others I can think of). In fact, it precluded me from posting another question.
Many, many thanks.
Regards.
NCM

 

[KenWright]

Using SUMPRODUCT to find the data anywhere in the string:-

=SUMPRODUCT(ISNUMBER(SEARCH("ABC",Code))*(Total))

Using SUMIF:-

=SUMIF(Code,"*"&"ABC"&"*",Total)

or with search string in a cell (A1)

=SUMIF(Code,"*"&A1&"*",Total)

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[NumberCrunchingMonky]

That works--thanks Ken!
Regards.
NCM