finding a name in a string

[jay d]

I have a function that looks for a particular name in a list of names
although it does not quite work. it is the 2nd part of the functio
that isnt working.


Code
-------------------
=SUMIF('Display By Catagory'!$F$9:$F$108,$B5,'Display By Catagory'!AU$9:AU$108)+SUMIF('Other Activities'!$F$8:$F$17,$B5,'Other Activities'!AU$8:AU$17)*IF(LEN(TRIM('Other Activities'!$F$8:$f$17))=0,0,LEN(TRIM('Other Activities'!$F$8:$f$17))-LEN(SUBSTITUTE('Other Activities'!$F$8:$f$17,";",""))+1
-------------------


the red bit is the problem.
other activities sheet-
f8:f17 is a list of names
b5 is a name to look for in the list of names
au8:au17 has numbers in it and these need to be added up and put in th
cell this code is in.

in the if bit, i want to check all rows to see if a name is in a cel
but it wont let me do this for multiple cells.

i want the code to look in a list of names, if the list contains
specific name, add up the numbers and put them in a cell, if there ar
multiple names to search through in a cell, eg if james is in the lis
simon, marc, james still to know the name is there and do the sum.

can anyone see what im doing wrong?

thank

 

[Don Guillett]

IF?? I understand you modify this to suit. To sum g for names in f
=SUMPRODUCT(($F$1:$F$4={"a","b","c"})*$G$1:$G$4)
to just count the number of them in F
=SUMPRODUCT(($F$1:$F$4={"a","b","c"})*1)