SOLVED

# Using functions to calculate hours in an Excel schedule

Copper Contributor

# Using functions to calculate hours in an Excel schedule

Hello, I'm going to try to explain what I'm looking for, so please be patient as I'm not particularly well versed in function commands for Excel. I am attempting to optimize a work schedule template that we use Excel for so that it calculates the hours an employee works in a given week. In the mock up below, I want to be able to calculate the total hours an employee works (let's say John, for example) in a week, despite them working a variety of days/hours (New York, Boston, etc.). I'm trying to get Excel to search for all instances of John's name in the chart from D4 to H21 and calculate the value of each cell that his name appears in. So in this example, John works an 8 hour New York run, an 8 hour Boston run, a 6 hour LA run, a 6 hour New Orleans run and a 5 hour Philadelphia run. I want to try to get the red box next to his name to display the total 33 hours that he worked for the week. I believe the SUMIF function will help me accomplish this, but so far when I attempt this with a function template I found on another forum (=SUMIF(D4:H21,"John",C4:C21), I get only the Mondays work for some people and 0's for others. I'm not entirely sure what the issue is here, or if I'm just asking too much of Excel. Hopefully I've explained this coherently and any input here would be very helpful. Thank you.

4 Replies

# Re: Using functions to calculate hours in an Excel schedule

best response confirmed by JohnDoe197 (Copper Contributor)
Solution

# Re: Using functions to calculate hours in an Excel schedule

SUMPRODUCT returns the intended result in my sample file.

# Re: Using functions to calculate hours in an Excel schedule

@JohnDoe197,

Is this resolved then?

Thanks,

# Re: Using functions to calculate hours in an Excel schedule

Thank you so much, this did the trick perfectly! This'll make my boss very happy tomorrow. Thanks for your help!
1 best response

Accepted Solutions
best response confirmed by JohnDoe197 (Copper Contributor)
Solution

# Re: Using functions to calculate hours in an Excel schedule

SUMPRODUCT returns the intended result in my sample file.