SUMPRODUCT containing txt string search

[sideshowjack]

Hello all
A B
Name code
EN A3
LL B4
EN C2
AB B3
EN,AB A3

column A values have a name of P1B and column B has values named P1F

I have the above data and Im trying to use it to count quantites of each
instance of code for each 2 letter name, e.g EN has 2 instances of code A3

Im using
=SUMPRODUCT((P1B="EN")*(P1F="B7"))

but I cant get the formula to count when there is more than one name in a
cell (eg EN,AB. I have tried using "*EN*" but doesnt work

Please help!

 

[Stefi]

Which Excel version do you use? In Excel2003 SUMPRODUCT doesn't handle entire
columns, in Excel2007 does.
Regards,
Stefi


„sideshowjack†ezt írta:

 

[sideshowjack]

Hi
I have excel '03, I have set the named columns to exact lengths (e.g
A2:A200), I can get SumPRODUCT to work with other sheets and formulae the
only problem I have is with it picking out a particular 2 letters from a cell
Thanks

 

[Stefi]

Try this:
=SUMPRODUCT(--NOT(ISERROR(FIND("EN",P1B))),--(B2:B6="P1F"))

Regards,
Stefi

„sideshowjack†ezt írta:

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(FIND("EN",P1B))),--(P1F="B7"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[sideshowjack]

Got it working thank you very much greatly appreciated

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„sideshowjack†ezt írta: