I would like to have my spreadsheet have number of hours for a shift, but
also identify which shift it is, for example:
11 ICU
10 CL
8 E
10.5 N

I need to be able to sum the hours, but also identify who is working what.
Help.

Hi,

Use this to sum the numbers

=SUMPRODUCT(--(TRIM(LEFT(A1:A4,FIND(" ",A1:A4)))))

to extract the letters

=TRIM(MID(A1,FIND(" ",A1),LEN(A1)))

drag down

Mike

" wrote:

I would like to have my spreadsheet have number of hours for a shift, but
also identify which shift it is, for example:
11 ICU
10 CL
8 E
10.5 N

I need to be able to sum the hours, but also identify who is working what.
Help.

Where is the info on who? Or am I missing something.

If the person in question is identified in column A and the hour/shift is in
column B then the formula to sum hours by shift and person would be:

=SUMPRODUCT(--(LEFT(B$1:B$100,FIND("
",B$1:B$100)-1)),--((MID(B$1:B$100,FIND("
",B$1:B$100)+1,9)=E1)),--(A$1:A$100=D1))

This assumes E1 contains the shift of the person you want totaled and D1
contains their name.

Enter the name and shifts of all the employees in columns D and E and copy
the formula down.

Another alternative would be to use the text to columns command (on the data
menu) to split the 11 ICU entries into two columns and write a simpler
formula or use a pivot table.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

I would like to have my spreadsheet have number of hours for a shift, but
also identify which shift it is, for example:
11 ICU
10 CL
8 E
10.5 N

I need to be able to sum the hours, but also identify who is working what.
Help.