sum ranges

[PaulPlumridge]

Hi
I'm sure this is a simple one but i cant figure it out. I need to drag a formula accross my row so that the results in each column are like this

1st row =sum(A1:A7)
2nd Row =sum(A8:A14)
3rd Row =sum(A15:21) etc

Thank you

 

[AlanY]

try this, and copy down

=SUM(OFFSET(A1,0,(ROW(A1)-1)*7,1,7))

 

[kweaver]

Where are you placing the formulas? If you have data in row 1, then a formula like =SUM(A1:G1) could be place in A2, etc. This would put the sum of the cells from and including A1 to G1 in cell A2. Is that would you mean?

 

[PaulPlumridge]

i have a large block of data which is ever date this year in row 2 with sales staff in one column. There is a sumifs from another sheet which gives all the commissions received each day.

I'm trying to have a summary which gives me their weekly sales.

 

[DanteAmor]

Hi
I'm sure this is a simple one but i cant figure it out. I need to drag a formula accross my row so that the results in each column are like this

1st row =sum(A1:A7)
2nd Row =sum(A8:A14)
3rd Row =sum(A15:21) etc

Thank you

According to your example, do you need this?

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:right; ">1</td><td style="text-align:right; ">28</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; text-align:right; ">2</td><td style="text-align:right; ">77</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; text-align:right; ">3</td><td style="text-align:right; ">126</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; text-align:right; ">4</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff00; text-align:right; ">5</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff00; text-align:right; ">6</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffff00; text-align:right; ">7</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffc000; text-align:right; ">8</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffc000; text-align:right; ">9</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffc000; text-align:right; ">10</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffc000; text-align:right; ">11</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffc000; text-align:right; ">12</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffc000; text-align:right; ">13</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffc000; text-align:right; ">14</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#92d050; text-align:right; ">15</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#92d050; text-align:right; ">16</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#92d050; text-align:right; ">17</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#92d050; text-align:right; ">18</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#92d050; text-align:right; ">19</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#92d050; text-align:right; ">20</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=SUM(OFFSET($A$1,((ROW()-1)*7),0,7))</td></tr></table></td></tr></table>

 

[Yongle]

To copy across a row try this in first cell to give SUM(A1:A7) and then drag or copy across th erow
=SUM(OFFSET($A$1,(COLUMN(A1)-1)*7,0,7))


Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​
1​	1​							formula in D2 copied across row
2​	1​			7​	14​	21​		=SUM(OFFSET($A$1,(COLUMN(A1)-1)*7,0,7))
3​	1​
4​	1​
5​	1​
6​	1​
7​	1​
8​	2​
9​	2​
10​	2​
11​	2​
12​	2​
13​	2​
14​	2​
15​	3​
16​	3​
17​	3​
18​	3​
19​	3​
20​	3​
21​	3​
22​

Sheet: Sheet2