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
--
Jay Taylor

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" wrote in message
...
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
--
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

--
Jay Taylor


"Barb Reinhardt" wrote:

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" wrote in message
...
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
--
Jay Taylor

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


Jay Taylor wrote:
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