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 function
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 the
cell this code is in.

in the if bit, i want to check all rows to see if a name is in a cell
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 a
specific name, add up the numbers and put them in a cell, if there are
multiple names to search through in a cell, eg if james is in the list
simon, marc, james still to know the name is there and do the sum.

can anyone see what im doing wrong?

thanks


--
jay d
------------------------------------------------------------------------
jay d's Profile: http://www.excelforum.com/member.php...o&userid=34487
View this thread: http://www.excelforum.com/showthread...hreadid=551185

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)
--
Don Guillett
SalesAid Software

"jay d" wrote in
message ...

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 function
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 the
cell this code is in.

in the if bit, i want to check all rows to see if a name is in a cell
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 a
specific name, add up the numbers and put them in a cell, if there are
multiple names to search through in a cell, eg if james is in the list
simon, marc, james still to know the name is there and do the sum.

can anyone see what im doing wrong?

thanks


--
jay d
------------------------------------------------------------------------
jay d's Profile:
http://www.excelforum.com/member.php...o&userid=34487
View this thread: http://www.excelforum.com/showthread...hreadid=551185