one last specification for the formula,if column a1 is "3cash 4office 1train"
,i want a2 which is the cash|office hrs column to add only the 3cash 4office
is = 7 and a3 which is the train|hiring|other hrs column to add only the
1train is = 1.
i have 6 different hrs columns with 3 or more different depts.
the order of depts can vary in order and combination.
eg)

a1 shift worked column a1= 3cash 4office 1other a1=3stock
2train 2pay
a2 cash|office|pay column a2= 7 a2=2
a3 stock|train|rec column a3= 0 a3=5
a4 other|front|ret|rel column a4=1 a4=0

"Ron Rosenfeld" wrote:

On Mon, 06 Feb 2006 21:17:25 -0500, Ron Rosenfeld
wrote:

On Mon, 6 Feb 2006 17:18:28 -0800, "cj" wrote:

oops, didn't install morefunc.
it works now, thanks.
is there any way i can include the words cash|office|stock to make a2 add up
the numbers only if any of those 3 dept are entered in a1?

Yes, there is.

But do you have any more specifications?
--ron

This is one way:

=IF(REGEX.FIND(A1,"cash|office|stock")0,EVAL(REGE X.SUBSTITUTE(A1&"0","(\D+)","+")),"")

But it'll be easiest if you could lay out all of your specifications at once,
for your project.


--ron