# Help calculating Toil or Flex

#### Kev7607

##### New Member
I'm trying to calculate the sum of hours in a row in a shift rosta
The staff put their hours of TOIL or FLEX in the rota eg. T1 T2 T4 T8 etc etc where staff have accrued hours toil, and F1 F2 F4 F8 etc where staff have accrued hours flex time.
At the end of the row Id like to see the total of hours toil and in the next column the total of hours flex.
Seems like it should be easier than im finding it

#### Kev7607

##### New Member

This is an example of what I'm trying to do - Number of hours in columns L an M

#### vletm

##### Excel Ninja
Kev7607
with clear known terms.

#### Kev7607

##### New Member
Unfortunately my network wont currently allow me to upload an excel file.
But looking at the screenshot that I posted. In Cell L3 I want to see the sum of Flex Hours for Fred Blogs (I know its 3 but want a formula to calculate it). In Cell M3 I want to see the sum of TOIL Hours for Fred Blogs (again I know its 13 but would like a formula to calculate it). Thanks

#### bosco_yip

##### Excel Ninja
Try...........................

1] In L3, array (CSE) formula copied down :

=SUM(IFERROR((LEFT(\$B3:\$K3)="F")*MID(\$B3:\$K3,2,99),0))

2] In M3, array (CSE) formula copied down :

=SUM(IFERROR((LEFT(\$B3:\$K3)="T")*MID(\$B3:\$K3,2,99),0))

p.s. Array (CSE) formula to be confirmed by pressing with CTRL + SHIFT + ENTER 3 keystrokes together instead of just Enter

Regards
Bosco

Last edited:

#### Kev7607

##### New Member
Thanks Bosco. Did exactly what I wanted. I don't understand it, but it works none the less.

#### MSC Bobs

##### Member
Kevin,

Also, when you have your data in a matrix like you do it can make finding what you want difficult because there are often intermediate steps in getting what you want.

Learning how to structure your files database-style will open up a whole new world for you. You won't feel constrained and your data can grow and expand naturally, rather than doing something like adding a new worksheet for every month. Cross-tab and matrix-style files really limit your ability to add records, look up exactly what you want, and are prone to gremlins and errors.

I can see someone already posted a solution, but I wanted to demonstrate how much cleaner and efficienter database file structures are.

#### Attachments

• 12.7 KB Views: 6

#### bosco_yip

##### Excel Ninja
Thanks Bosco. Did exactly what I wanted. I don't understand it, but it works none the less.
Another simpler formula option

In order to avoid complicated formula, another database setup is shown below with adding a new column of "Flex/Toil" in col B for your reference.

Then,

in M3, formula : =IF(\$B3="Flex",SUM(\$C3:\$L3),"")

And,

in M3, formula : =IF(\$B3="Toil",SUM(\$C3:\$L3),"")

All copied down

Regards
Bosco