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

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

fredieusa

New Member
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

Schedule%20Pic_zpsyzpdssoh.jpg
 

Attachments

  • RPA Schedules 20170605.xlsx
    20.5 KB · Views: 16
B25: =SUMPRODUCT(($C$3:$U$22=$A25)*IFERROR($E$3:$W$22-$D$3:$V$22,0)*24)
Ctrl+Shift+Enter

Copy down
 
You BOTH are sorcerers! Where lies your powers?


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

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?


fredieusa
Something like this?

Thank you, I know almost NOTHING about 'macros' I am on this now.
 
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
 
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
 
Back
Top