1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Kev7607, Mar 14, 2019.

  1. Kev7607

    Kev7607 New Member

    Messages:
    11
    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 :)
  2. Kev7607

    Kev7607 New Member

    Messages:
    11
    upload_2019-3-14_11-35-43.png
    This is an example of what I'm trying to do - Number of hours in columns L an M
  3. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Kev7607
    Upload an Excel Sample -file!
    with clear known terms.
  4. Kev7607

    Kev7607 New Member

    Messages:
    11
    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
  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,191
    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: Mar 15, 2019 at 2:46 AM
    Thomas Kuriakose and Kev7607 like this.
  6. Kev7607

    Kev7607 New Member

    Messages:
    11
    Thanks Bosco. Did exactly what I wanted. I don't understand it, but it works none the less.
  7. MSC Bobs

    MSC Bobs Member

    Messages:
    48
    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.

    Attached Files:

    Kev7607 likes this.
  8. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,191
    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
    Kev7607 and Thomas Kuriakose like this.

Share This Page