| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT containing txt string search
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT containing txt string search
Which Excel version do you use? In Excel2003 SUMPRODUCT doesn't handle entire
columns, in Excel2007 does. Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT containing txt string search
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" wrote: Which Excel version do you use? In Excel2003 SUMPRODUCT doesn't handle entire columns, in Excel2007 does. Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT containing txt string search
Try this:
=SUMPRODUCT(--NOT(ISERROR(FIND("EN",P1B))),--(B2:B6="P1F")) Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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" wrote: Which Excel version do you use? In Excel2003 SUMPRODUCT doesn't handle entire columns, in Excel2007 does. Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT containing txt string search
=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" wrote in message ... 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" wrote: Which Excel version do you use? In Excel2003 SUMPRODUCT doesn't handle entire columns, in Excel2007 does. Regards, Stefi "sideshowjack" ezt ķrta: 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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT containing txt string search
Got it working thank you very much greatly appreciated
"Stefi" wrote: Try this: =SUMPRODUCT(--NOT(ISERROR(FIND("EN",P1B))),--(B2:B6="P1F")) Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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" wrote: Which Excel version do you use? In Excel2003 SUMPRODUCT doesn't handle entire columns, in Excel2007 does. Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT containing txt string search
You are welcome! Thanks for the feedback!
Stefi €˛sideshowjack€¯ ezt Ć*rta: Got it working thank you very much greatly appreciated "Stefi" wrote: Try this: =SUMPRODUCT(--NOT(ISERROR(FIND("EN",P1B))),--(B2:B6="P1F")) Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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" wrote: Which Excel version do you use? In Excel2003 SUMPRODUCT doesn't handle entire columns, in Excel2007 does. Regards, Stefi €˛sideshowjack€¯ ezt Ć*rta: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to search a string from the right ? | Setting up and Configuration of Excel | |||
| to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
| SUMPRODUCT and search string | Excel Worksheet Functions | |||
| Q: search in string | Excel Discussion (Misc queries) | |||
| Segregating a Search String | Excel Discussion (Misc queries) | |||
Linear Mode
