Dragging SUM formula

Hi there,

A simple question but I can't for the life of me figure it out.

I have this formula to add up one weeks worth of shifts: =SUM('Shift Data'!D3:D16)
I want the row below to add up the following week, thus: =SUM('Shift Data'!D17:D30)

However, when I went to drag the formula down from there it does this:
=SUM('Shift Data'!D5:D18)
=SUM('Shift Data'!D19:D32)
=SUM('Shift Data'!D7:D20)

It's increasing the cell reference by x each time (x being the number of cells I highlight before dragging down) so in this case it increases both by two as I'm highlighting two cells.

Is there any way to get excel to automatically copy the formula and get it to change the range to the next 14 cells? Or is it a manual job?

Thanks

What cell is your formula in, you could probably utilise indirect and row functions to get what you want. For example, if in A1 you have:
=sum('shift Data'!D3:D16)
and in A2 you want:
=sum('shift Data'!D17:D30)
then change A1 to :
=sum(indirect("'Shift Data'!D"& (ROW(A1)-1)*14+3 & ":D" & (ROW(A1)-1)*14+16))

Please Login or Register  to view this content.

Originally Posted by yudlugar

What cell is your formula in, you could probably utilise indirect and row functions to get what you want. For example, if in A1 you have:
=sum('shift Data'!D3:D16)
and in A2 you want:
=sum('shift Data'!D17:D30)
then change A1 to :
=sum(indirect("'Shift Data'!D"& (ROW(A1)-1)*14+3 & ":D" & (ROW(A1)-1)*14+16))

Thank you, this seems to work perfectly. Though I confess, I'm not sure why.

I have no experience of the 'indirect' function so I may be missing something. The cell that I am beginning with is B3. Leaving the A1 in your formula as it is makes the formula work fine, changing it to B3 makes it reference different cells (and returns the wrong answer), changing it to B1 makes it work fine. However A1 and B1 both have writing in or are blank. There must be something about it that I don't understand.

However, I have several columns and have copied it across, changing the column reference in the shift data sheet for each and it has worked for them all, so thank you.

Hi, the =indirect() function takes a reference as a string and converts it into an actual reference you can use in a function.

Therefore, if you evaluate what is inside the brackets of the indirect function it needs to return the address of the range you want to reference.

So:
("'Shift Data'!D"& (ROW(A1)-1)*14+3 & ":D" & (ROW(A1)-1)*14+16)
Row(A1()) = 1
therefore:
("'Shift Data'!D"& (1-1)*14+3 & ":D" & (1-1)*14+16)
which is:
("'Shift Data'!D3:D16)

then when you drag down to the next cell, row(a1) becomes row(a2) which =2 so:
("'Shift Data'!D"& (2-1)*14+3 & ":D" & (2-1)*14+16)
which is:
("'Shift Data'!D7:D30)
and so on.

If you start in a different row, then you need to update what is in the indirect brackets as appropriate, as you mention, leaving it as row(a1) would work fine, but if you start at 3 you wil skip the first two sets or data.

Excellent. Now makes perfect sense and definitely something to remember for the future. Much obliged!

Martin, I found a formula that worked so didn't try yours out but I'm sure it's equally wonderful. Thank you as well.

Is there any pracitcal difference between the two? Only I have now covered my spreadsheet with the other formula.

Unfortunately I have a new, related, question. I would now like to do the same thing, but for months. Because the difference in days means that one formula will end up missing a day here and there. Originally I was going to just do every 4 weeks but my boss would like it to be calender months, alas...

So, it's the same idea, to SUM all the numbers within a range that is flexible (thus takes the start date and keeps adding numbers until it reaches the last date of the same month).

Thanks again

Generally, arguements agains using indirect is that it is difficult to follow/audit. It can make the formula auditing tool difficult to use for example.

I would suggest it is not such an issue in this case though...

Do you have the dates listed on your spreadsheet somewhere, you could use a =sumif() based on the date you wish to look at.

It would be helpful if you could upload a workbook..

Attached.

So yeah, the dates are listed on worksheet - two times per day for each shift (however there may be more than this, so the number of dates per month can not be relied upon - and if someone does not record the data then of course whole days may be missing). Which makes me wonder if the sumif approach should be used for the weekly data as well as the current method would be susceptible to missing data.

The Sumif would work, just I'm not sure how to set it to go between two dates. Sumproduct would also work I guess? Can something similar be done for average? I'm on excel 2003 and I seem to recall that there is no averageif function.

in B4:
=SUMPRODUCT(IF('Shift Data'!$A$3:$A$113>'Shift Monthly'!$A4,'Shift Data'!D$3:D$113,0)*IF('Shift Data'!$A$3:$A$113<'Shift Monthly'!$A5,1,0))
In C4:
=B4/SUMPRODUCT(IF('Shift Data'!$A$3:$A$113>'Shift Monthly'!$A4,1,0)*IF('Shift Data'!$A$3:$A$113<'Shift Monthly'!$A5,1,0))

Both are array formulas (confirm with ctrl+shift+enter)

Beautiful work.
Appears to work perfectly.

Thank you.