• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
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...........................

upload_2019-3-14_21-8-4.png

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:

MSC Bobs

Member
Kevin,

One thing you can do to help yourself is segregate your data from your calculations.

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

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.

upload_2019-3-15_11-45-0.png

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
 
Top