I am trying to shorten a formula that runs across two other sheets.
The sheets track hours worked by pay period, two pay periods per sheet. I have a subtotal line that shows the total hours for that pay period, but some payperiods contain hours from two different months.
On a summary sheet, I want to show how many hours were worked in any given month, rather than by pay periods.
Each Pay Period sheet has two cells from each row I need to put in the formula, regular hours and overtime hours for each day of the month..(cells D & E)

The only way I can get the formula to work is to select cells D & E, then the plus sign, then the next row, cells D & E. I have to repeat this 30 times.
I cannot get it to work using a colon or comma to shorten the formula.

The formula ends up looking like this: (sheet tabs are named one, two three & summary, that's where the formula goes)
=SUM(one!D32:E32+two!D17:E17+two!D18:E18+two!D19:E 19+two!D20:E20)

Shouldn't I be able to have D17 to E20 all in one bunch? Or at least D17:20 and E17:E20.
I get a #value! error.
File is attached

Attached Files

formula test.xls (41.5 KB, 24 views)