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.

Total Hours: =COUNTIF / =SUM( or ???

Discussion in 'Ask an Excel Question' started by fredieusa, Jun 5, 2017.

  1. fredieusa

    fredieusa New Member

    Messages:
    7
    Hello again everyone!

    I am trying to calculate total hours for employees. I was the hours to be calculated based on the hours for the week.

    I would like to change the hours as sometimes employees get off late and are also sometimes called in early, these changes to be reflected in the total hours worked for the week.

    Thanks in anticipation

    [​IMG]

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,533
    B25: =SUMPRODUCT(($C$3:$U$22=$A25)*IFERROR($E$3:$W$22-$D$3:$V$22,0)*24)
    Ctrl+Shift+Enter

    Copy down
  3. vletm

    vletm Well-Known Member

    Messages:
    2,714

    Attached Files:

    Thomas Kuriakose likes this.
  4. fredieusa

    fredieusa New Member

    Messages:
    7
    You BOTH are sorcerers! Where lies your powers?


    Thank you, tried finding out why Ctrl+Shift+Enter gets me the results and I seem not to get it. Do you have a name for the formulas so I can read up and better understand this process please?


    Thank you, I know almost NOTHING about 'macros' I am on this now.
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,533
    Ctrl+Shift+Enter tells excel that this formula is to be treated as an Array Formula

    If you click on the cell and look at the Formula bar you will see {} around the formula, this is Excel's way of showing us it is an Array Formula

    An Array Formula is as it says a Formula that works on an Array of Values
    This allows it for example to sum up the differences in your times (End - Start)

    You can read more about array formulas by reading these two posts
    http://www.excel-easy.com/functions/array-formulas.html
    http://www.cpearson.com/excel/arrayformulas.aspx

    In your example it is a single cell Array Formula
    Thomas Kuriakose likes this.
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,533
    I have also just noticed an error in my formula; Grrrr :(


    In the cells where the Start Time is late PM and the Finish time is the next day, early am, the formula is incorrect

    So change cell B25 to
    =SUMPRODUCT(($C$3:$U$22=$A25)*IFERROR(IF($E$3:$W$22<$D$3:$V$22,($E$3:$W$22)+1,$E$3:$W$22)-$D$3:$V$22,0)*24) Ctrl+Shift+Enter

    Then copy down
    Thomas Kuriakose likes this.
  7. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    355
    Hi, to all!

    You can use this too (CSE --> Ctrl + Shift + Enter):
    =SUM(($C$3:$U$22=A25)*IFERROR(24*MOD($E$3:$W$22-$D$3:$V$22,1),))

    Blessings!
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,533
    Thats nice John!

Share This Page