adding text

[Jay Taylor]

I am building a work schedule. I have different text and numerical codes for
shifts (ob1, ob2, n, sac, 9-5, 1, AND ER) I want the schedule to add only the
text I have in parenthesis. I want this to be a numerical value. I want it to
ignore my day off code and Holiday Time(do, ht) Also after this I want
another cell to show 12- the answer I received from the added days above.
I do not want to change the format of my schedule. It has been used for years.
I am using Microsoft Excel 2003 98xp pro

 

[Barb Reinhardt]

I'm not clear on what you want.

First you say you want the schedule to add only the text in parens
Then you say you want the entry to be a numeric value.

The text in parents is not numeric. Do you want to convert it to numeric
somehow? A piece of this puzzle is missing.

We'll get to the 12 later.

 

[Jay Taylor]

ob1 & ob2 needs to equal 12, n,sac,9-5,1,and er needs to equal 8.
1 2 3 4 5
worker 1 ob1 9-5 1 ob2 n should equal 48
worker 2 n 1 9-5 ob1 ob2 should equal 48

 

[Roger Govier]

Hi Jay

With your data in A1:F3, enter in cell H2
=SUMPRODUCT(--(B1:F1<>"")*8)+SUMPRODUCT(--(LEFT(B1:F1,2)="ob")*4)
and copy down

Regards

Roger Govier