# How to create sub-totals in Excel using SUM()?

It's been a long time since I needed to use Excel and I need a little help with copying a formula.

This is a simplified explanation of what I'm trying to do:

`A1:365` has daily sales figures in it.

In column B I want to calculate the weekly sales, therefore the formula in B1 is `=SUM(A1:A7)`

If I copy the formula down B2 changes to `=SUM(A2:A8)`, but I would like it to say `=SUM(A8:A14)`

Can this be done without the need to re-type the formula 52 times?

As already mentioned `INDIRECT()` is an option. Another is `OFFSET()`...

So B1 would become

``````=SUM(OFFSET(\$A\$1,(ROW()-1)*7,0,7,1))
``````

and copy down as before.

Deconstructing a little, `OFFSET(reference, rows, cols, height, width)` defines a range that starts `rows` down and `cols` across from `reference` whose dimensions are `height` rows and `width` columns. (The last two are optional if you only want a 1x1 range). `ROW()` with no argument returns the row number of the cell containing the function, so in the case above it returns 1 in B1, 2 in B2 etc, so we just subtract 1 and multiply by our desired size (7 in this case) to figure out where in the list our target for summing should start.

• Wow, and so much simpler than my solution!!! +1 Aug 4, 2011 at 8:51
• @stema - keep it a secret: `OFFSET` is the source of much of my Excel-fu... ;-) Aug 4, 2011 at 8:54
• Bingo! Perfect, works first time. Thanks for the help.
– Darren Cook
Aug 4, 2011 at 8:57

Yes it can be done.

Try this here:

``````=SUM(INDIRECT(ADDRESS((CELL("row",B1)-1)*7+1,1)):INDIRECT(ADDRESS((CELL("row",B1))*7,1)))
``````

Edit:

thanks to Mike Woodhouse (see his answer) I can simplify my solution a bit.

`Row()` (in the cell "B1") will do the same than `CELL("row",B1)` so

``````=SUM(INDIRECT(ADDRESS((ROW()-1)*7+1,1)):INDIRECT(ADDRESS(ROW()*7,1)))
``````

will do the same a bit simpler.

Explanation:

You need to calculate the addresses then.

`(CELL("row",B1)-1)*7+1` will give you the start cell. `CELL("row",B1)` gives me the number of the current row. Subtract 1 multiply by 7 and add 1 will give for each row the starting daily row as a number.

`ADDRESS((CELL("row",B1)-1)*7+1,1)` is making an address string from numbers, I give the calculated starting row number and the column (the 1 at the end)

`INDIRECT(ADDRESS((CELL("row",B1)-1)*7+1,1))` indirect is making a range from a string.

The same than for the last wanted cell. `INDIRECT(ADDRESS((CELL("row",B1))*7,1))` the difference is only the calculation of the ending row.

Then I give the whole thing to `Sum` and I am done. Just copy it down 52 times and you have the sum for every week. • Wow, would never have been able to work that out! Even with your great explanation it starts to make my brain hurt!
– Darren Cook
Aug 4, 2011 at 8:28
• You are welcome, just vote me up and accept if it is working ;). If you have any questions to understand it or to get it working just ask. Aug 4, 2011 at 8:32
• Just pasted the formula into my workbook and it gives an error (highlights "row" in black) I'm using Excel 2010. Can see from your capture that it's working for you though??
– Darren Cook
Aug 4, 2011 at 8:37
• I am still using Excel 2003, but I checked on microsoft.com, 2010 does still have this function. Did you paste into cell "B1"? What happens if you just copy `=CELL("row";B1)` into another cell? (should give you `1` as result) Aug 4, 2011 at 8:46
• Yes, was in B1. Tried the =CELL("row";B1) but same error. Also tried saving as 97-2003 workbook but no joy.
– Darren Cook
Aug 4, 2011 at 8:54