Allocating a value to text

[Steve]

I have a to work out a rota.

A night shift is put down as an N and is 15 hours long. Day Shift D 10
hours long and L 5 hours long. I need to work out peoples hours. I
don't know how to get excel to allocate a value to N, D & L so it can
do a sum.


FOR EXAMPLE: (I need a sum of hours worked in column H)
A B C D E F G H
1 N N N Off Off Off
2 L L L D D Off
3 D D D Off Off Off
4
5
6
7
8
9


Many thanks

Steve
(A poor over worked Nurse!)

 

[Anne Troy]

Try this:

=(COUNTIF(A1:E1,"n")*15)+(COUNTIF(A1:E1,"d")*10)+(COUNTIF(A1:E1,"l")*5)

************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

 

[Bob Phillips]

Or even

=SUMPRODUCT(COUNTIF(A1:E1,{"N","D","L"}),{15,10,5})

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Anne Troy]

Some day, I will learn how to effectively use sumproduct.
Right after I learn pivot tables better.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

 

[Bob Phillips]

Debra Dalgleish has a book on pivot tables due on March 6th. I will
certainly be looking at that.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)