Help with array formulas, how to encode multiple summed series within one range of cells

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi,

Why do you want to store this data in a single cell? Not only are you stacking up problems for yourself, you're making it unnecessarily complicated and restricting the sort of powerful analysis a Pivot Table can bring.
Why not just create a normalised 2 dimensional table that has columns for

Date
Type of Hours ' entries here would be NB, SL,...etc,
Number of hours


and create a record for each unique combination so that whereas in your single cell example you have 3 types of hour, in a database proper there would be 3 row records.

Hi all,

Sorry for the week's silence. Unfortunately this is how my company wants to display our data, and the same format is used across the team.

For what it's worth, this is how our timesheet is laid out, where each row is one employee.
Untitled.png

I've got my formulas working for a week now:
(For Billable) =SUM(IF(ISNUMBER(LEFT($P5:$AT5,1)+0),LEFT($P5:$AT5,IFERROR(FIND(" ",$P5:$AT5),LEN($P5:$AT5)))+0,0))
(For NonBillable) =SUM(IFERROR(SUBSTITUTE(LEFT(MID($P5:$AT5&" ",FIND(AW$3,$P5:$AT5),255),FIND(" ",MID($P5:$AT5&" ",FIND(AW$3,$P5:$AT5),255))),AW$3,"")+0,0))+COUNTIF($P5:$AT5,AW$3)*8
(For AL, SL, UL) =SUM(IFERROR(SUBSTITUTE(LEFT(MID($P5:$AT5&" ",FIND(AX$3,$P5:$AT5),255),FIND(" ",MID($P5:$AT5&" ",FIND(AX$3,$P5:$AT5),255))),AX$3,"")+0,0))+COUNTIF($P5:$AT5,AX$3)*8