| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count string IF.....
I am trying to count a 3 digit embeded data string IF....
NAME DATA_STRING Joe WAPDATMM4RT1RRR Susan WAPDATPLBMM5UDF John DATPLBINSRBTMM6 Joe WAPUDARBTRRRMM4 I can count the strings with... =SUM(COUNTIF(B2:B5,"*WAP*"),COUNTIF(B2:B5,"*RBT*") ,COUNTIF(B2:B5,"*MM4*")) result (7) I need the result IF A2:A5=Joe result (5) I have stared at SUMPRODUCT long enough....H E L P ! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count string IF.....
One way:
=SUMPRODUCT((ISNUMBER(FIND({"WAP","RBT","MM4"},B2: B5)))*(A2:A5="Joe")) Replace FIND with SEARCH if you don't need it to be case sensitive -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Coley" wrote: I am trying to count a 3 digit embeded data string IF.... NAME DATA_STRING Joe WAPDATMM4RT1RRR Susan WAPDATPLBMM5UDF John DATPLBINSRBTMM6 Joe WAPUDARBTRRRMM4 I can count the strings with... =SUM(COUNTIF(B2:B5,"*WAP*"),COUNTIF(B2:B5,"*RBT*") ,COUNTIF(B2:B5,"*MM4*")) result (7) I need the result IF A2:A5=Joe result (5) I have stared at SUMPRODUCT long enough....H E L P ! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count string IF.....
OUTSTANDING! Not sure why it works but it does. Thanks!
"Max" wrote: One way: =SUMPRODUCT((ISNUMBER(FIND({"WAP","RBT","MM4"},B2: B5)))*(A2:A5="Joe")) Replace FIND with SEARCH if you don't need it to be case sensitive -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Coley" wrote: I am trying to count a 3 digit embeded data string IF.... NAME DATA_STRING Joe WAPDATMM4RT1RRR Susan WAPDATPLBMM5UDF John DATPLBINSRBTMM6 Joe WAPUDARBTRRRMM4 I can count the strings with... =SUM(COUNTIF(B2:B5,"*WAP*"),COUNTIF(B2:B5,"*RBT*") ,COUNTIF(B2:B5,"*MM4*")) result (7) I need the result IF A2:A5=Joe result (5) I have stared at SUMPRODUCT long enough....H E L P ! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count string IF.....
welcome, Coley. glad it helped.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Coley" wrote in message ... OUTSTANDING! Not sure why it works but it does. Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count string IF.....
"Max" wrote...
One way: =SUMPRODUCT((ISNUMBER(FIND({"WAP","RBT","MM4"},B2 :B5)))*(A2:A5="Joe")) .... Another way, shorter but an array formula, =COUNT(FIND({"WAP","RBT","MM4"},B2:B5)/(A2:A5="Joe")) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count characters within a string | Excel Worksheet Functions | |||
| Count rows with a string in any cell | Excel Worksheet Functions | |||
| count number of letters in a string | Excel Worksheet Functions | |||
| Colomn of strings, how to count each string with "abc" OR "def" | Excel Worksheet Functions | |||
| count number of occurences within a string | Excel Worksheet Functions | |||
Linear Mode
