# 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

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

#### Attachments

• RPA Schedules 20170605.xlsb
56 KB · Views: 15
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)

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

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!

Thats nice John!