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

File size:
20.5 KB
Views:
15
2. ### HuiExcel NinjaStaff Member

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

Copy down
3. ### vletmWell-Known Member

Messages:
2,710

#### Attached Files:

• ###### RPA Schedules 20170605.xlsb
File size:
56 KB
Views:
11
Thomas Kuriakose likes this.
4. ### fredieusaNew 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. ### HuiExcel NinjaStaff Member

Messages:
10,532
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. ### HuiExcel NinjaStaff Member

Messages:
10,532
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 VWell-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. ### HuiExcel NinjaStaff Member

Messages:
10,532
Thats nice John!