# 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

• 20.5 KB Views: 15

#### Hui

##### Excel Ninja
Staff member
B25: =SUMPRODUCT((\$C\$3:\$U\$22=\$A25)*IFERROR(\$E\$3:\$W\$22-\$D\$3:\$V\$22,0)*24)
Ctrl+Shift+Enter

Copy down

#### Attachments

• 56 KB Views: 12
• Thomas Kuriakose

#### fredieusa

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

#### Hui

##### Excel Ninja
Staff member
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

#### Hui

##### Excel Ninja
Staff member
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

#### John Jairo V

##### Well-Known Member
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!

Staff member
Thats nice John!