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)

Hello,

You may try to use sumif. I have included a column in sheets one, two and three indicating which month the payment belongs to (using MONTH formula). Then writing a SUMIF formula once should solve all your problems You may check the attached excel.

Attached Files

formula_test_sumif.xls (47.0 KB, 21 views)

Thanks mugezhn, this will take me awhile to digest. I assume I need the 'month' column in all my sheets. Although this formula is still complicated, I assume the advantage over mine is that I only have to make this once, then drag the formula down the column?

Yes, you need Month formula in all your sheets but you can change the column in which you place it. As for the formula, once you write it dragging down would be enough.

Thanks again. Have been integrating it into all sheets and it's working fine.