Add up every 7 lines, then the next 7

[jonathan92591]

Hello everyone,

Add up every 7 lines, then the next 7

--

Excel or Access version: Excel 2010

Computer operating system: Windows XP

Sample data: "

Excel Workbook
	A	B	C	D	E	F	G	H
1	MC	Day	Date	Revenue	Day	MC-CYSP-Summer	CYSP	7 Days of Revenue
2	MC-CYSP-Summer	Day 1	8/15/2011	$30	Day 1	$30	$30	*
3	MC-CYSP-Summer	Day 2	8/16/2011	$90	Day 2	$90	$120	*
4	MC-CYSP-Summer	Day 3	8/17/2011	$30	Day 3	$30	$150	*
5	MC-CYSP-Summer	Day 4	8/18/2011	$990	Day 4	$990	$1,140	*
6	MC-CYSP-Summer	Day 5	8/19/2011	$390	Day 5	$390	$1,530	*
7	MC-CYSP-Summer	Day 6	8/20/2011	$510	Day 6	$510	$2,040	*
8	MC-CYSP-Summer	Day 7	8/21/2011	$910	Day 7	$910	$2,950	*
9	MC-CYSP-Summer	Day 8	8/22/2011	$470	Day 8	$470	$3,419	*
10	MC-CYSP-Summer	Day 9	8/23/2011	$800	Day 9	$800	$4,219	*
11	MC-CYSP-Summer	Day 10	8/24/2011	$450	Day 10	$450	$4,669	*
12	MC-CYSP-Summer	Day 11	8/25/2011	$390	Day 11	$390	$5,059	*
13	MC-CYSP-Summer	Day 12	8/26/2011	$180	Day 12	$180	$5,239	*
14	MC-CYSP-Summer	Day 13	8/27/2011	$490	Day 13	$490	$5,729	*
15	MC-CYSP-Summer	Day 14	8/28/2011	$990	Day 14	$990	$6,719	*
Backend

"

Formula(s) right now: Don't have one yet.

Current result(s): N/A

My goal: To sum up Day1 through Day7, Day 8 through Day14, etc.

Error message: No error message

How error occurred: No error message

Generated in: Excel

Thank you.

JT

 

[jproffer]

It's not as hard as it seems.

Excel 2007
	A	B	C	D	E	F	G	H	I
1	1	MC	Day	Date	Revenue	Day	MC-CYSP-Summer	CYSP	7 Days of Revenue
2	2	MC-CYSP-Summer	Day 1	8/15/2011	$30	Day 1	$30	$30	*
3	3	MC-CYSP-Summer	Day 2	8/16/2011	$90	Day 2	$90	$120	*
4	4	MC-CYSP-Summer	Day 3	8/17/2011	$30	Day 3	$30	$150	*
5	5	MC-CYSP-Summer	Day 4	8/18/2011	$990	Day 4	$990	$1,140	*
6	6	MC-CYSP-Summer	Day 5	8/19/2011	$390	Day 5	$390	$1,530	*
7	7	MC-CYSP-Summer	Day 6	8/20/2011	$510	Day 6	$510	$2,040	*
8	8	MC-CYSP-Summer	Day 7	8/21/2011	$910	Day 7	$910	$2,950	$2,950
9	9	MC-CYSP-Summer	Day 8	8/22/2011	$470	Day 8	$470	$3,419	*
10	10	MC-CYSP-Summer	Day 9	8/23/2011	$800	Day 9	$800	$4,219	*
11	11	MC-CYSP-Summer	Day 10	8/24/2011	$450	Day 10	$450	$4,669	*
12	12	MC-CYSP-Summer	Day 11	8/25/2011	$390	Day 11	$390	$5,059	*
13	13	MC-CYSP-Summer	Day 12	8/26/2011	$180	Day 12	$180	$5,239	*
14	14	MC-CYSP-Summer	Day 13	8/27/2011	$490	Day 13	$490	$5,729	*
15	15	MC-CYSP-Summer	Day 14	8/28/2011	$990	Day 14	$990	$6,719	$3,769
16	..	..	..	..	..	..	..	..	.
Sheet1

Cell Formulas
Range	Formula
I8	=H8
I15	=H15-I8

Enter the two formulas as shown, then select the highlighted cells, then drag down as far as you like.

 

[jproffer]

Or if you meant a rolling total for the most recent 7 days, try this:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD>MC</TD><TD>Day</TD><TD>Date</TD><TD>Revenue</TD><TD>Day</TD><TD>MC-CYSP-Summer</TD><TD>CYSP</TD><TD>7 Days of Revenue</TD><TD>rolling last 7 day total</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD>MC-CYSP-Summer</TD><TD>Day 1</TD><TD style="TEXT-ALIGN: right">8/15/2011</TD><TD style="TEXT-ALIGN: right">$30 </TD><TD>Day 1</TD><TD style="TEXT-ALIGN: right">$30 </TD><TD style="TEXT-ALIGN: right">$30 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2950</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD>MC-CYSP-Summer</TD><TD>Day 2</TD><TD style="TEXT-ALIGN: right">8/16/2011</TD><TD style="TEXT-ALIGN: right">$90 </TD><TD>Day 2</TD><TD style="TEXT-ALIGN: right">$90 </TD><TD style="TEXT-ALIGN: right">$120 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">3390</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD>MC-CYSP-Summer</TD><TD>Day 3</TD><TD style="TEXT-ALIGN: right">8/17/2011</TD><TD style="TEXT-ALIGN: right">$30 </TD><TD>Day 3</TD><TD style="TEXT-ALIGN: right">$30 </TD><TD style="TEXT-ALIGN: right">$150 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">4100</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD>MC-CYSP-Summer</TD><TD>Day 4</TD><TD style="TEXT-ALIGN: right">8/18/2011</TD><TD style="TEXT-ALIGN: right">$990 </TD><TD>Day 4</TD><TD style="TEXT-ALIGN: right">$990 </TD><TD style="TEXT-ALIGN: right">$1,140 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">4520</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">6</TD><TD>MC-CYSP-Summer</TD><TD>Day 5</TD><TD style="TEXT-ALIGN: right">8/19/2011</TD><TD style="TEXT-ALIGN: right">$390 </TD><TD>Day 5</TD><TD style="TEXT-ALIGN: right">$390 </TD><TD style="TEXT-ALIGN: right">$1,530 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">3920</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">7</TD><TD>MC-CYSP-Summer</TD><TD>Day 6</TD><TD style="TEXT-ALIGN: right">8/20/2011</TD><TD style="TEXT-ALIGN: right">$510 </TD><TD>Day 6</TD><TD style="TEXT-ALIGN: right">$510 </TD><TD style="TEXT-ALIGN: right">$2,040 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">3710</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">8</TD><TD>MC-CYSP-Summer</TD><TD>Day 7</TD><TD style="TEXT-ALIGN: right">8/21/2011</TD><TD style="TEXT-ALIGN: right">$910 </TD><TD>Day 7</TD><TD style="TEXT-ALIGN: right">$910 </TD><TD style="TEXT-ALIGN: right">$2,950 </TD><TD style="TEXT-ALIGN: right">$2,950 </TD><TD style="TEXT-ALIGN: right">3690</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">9</TD><TD>MC-CYSP-Summer</TD><TD>Day 8</TD><TD style="TEXT-ALIGN: right">8/22/2011</TD><TD style="TEXT-ALIGN: right">$470 </TD><TD>Day 8</TD><TD style="TEXT-ALIGN: right">$470 </TD><TD style="TEXT-ALIGN: right">$3,419 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">3770</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">10</TD><TD>MC-CYSP-Summer</TD><TD>Day 9</TD><TD style="TEXT-ALIGN: right">8/23/2011</TD><TD style="TEXT-ALIGN: right">$800 </TD><TD>Day 9</TD><TD style="TEXT-ALIGN: right">$800 </TD><TD style="TEXT-ALIGN: right">$4,219 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">3300</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">11</TD><TD>MC-CYSP-Summer</TD><TD>Day 10</TD><TD style="TEXT-ALIGN: right">8/24/2011</TD><TD style="TEXT-ALIGN: right">$450 </TD><TD>Day 10</TD><TD style="TEXT-ALIGN: right">$450 </TD><TD style="TEXT-ALIGN: right">$4,669 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">2500</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">12</TD><TD>MC-CYSP-Summer</TD><TD>Day 11</TD><TD style="TEXT-ALIGN: right">8/25/2011</TD><TD style="TEXT-ALIGN: right">$390 </TD><TD>Day 11</TD><TD style="TEXT-ALIGN: right">$390 </TD><TD style="TEXT-ALIGN: right">$5,059 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">2050</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">13</TD><TD>MC-CYSP-Summer</TD><TD>Day 12</TD><TD style="TEXT-ALIGN: right">8/26/2011</TD><TD style="TEXT-ALIGN: right">$180 </TD><TD>Day 12</TD><TD style="TEXT-ALIGN: right">$180 </TD><TD style="TEXT-ALIGN: right">$5,239 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">1660</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">14</TD><TD>MC-CYSP-Summer</TD><TD>Day 13</TD><TD style="TEXT-ALIGN: right">8/27/2011</TD><TD style="TEXT-ALIGN: right">$490 </TD><TD>Day 13</TD><TD style="TEXT-ALIGN: right">$490 </TD><TD style="TEXT-ALIGN: right">$5,729 </TD><TD>*</TD><TD style="TEXT-ALIGN: right">1480</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">15</TD><TD>MC-CYSP-Summer</TD><TD>Day 14</TD><TD style="TEXT-ALIGN: right">8/28/2011</TD><TD style="TEXT-ALIGN: right">$990 </TD><TD>Day 14</TD><TD style="TEXT-ALIGN: right">$990 </TD><TD style="TEXT-ALIGN: right">$6,719 </TD><TD style="TEXT-ALIGN: right">$3,769 </TD><TD style="TEXT-ALIGN: right">990</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">……………..</TD><TD style="COLOR: #ffffff">…………………………….</TD><TD style="COLOR: #ffffff">……………………………………..</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>I8</TH><TD style="TEXT-ALIGN: left">=H8</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>J2</TH><TD style="TEXT-ALIGN: left">=SUM(OFFSET(E2,0,0,7))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>I15</TH><TD style="TEXT-ALIGN: left">=H15-I8</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>J15</TH><TD style="TEXT-ALIGN: left">=SUM(OFFSET(E15,0,0,7))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Oops, don't worry about the formulas in I8 and I15, if that's what you want.

 

[jonathan92591]

Hello,

I use the "offset" formula and it works beautifully! I will have to learn how "offset" works. Thank you kindly!

JT

 

[jproffer]

It's really pretty handy if you know how to use it. The last 2 arguments are height and width, so it's not just moving down or over, it's extending the range down or over.

Anyhow, very handy I would recommend looking it up and getting familiar with it.

Glad it worked for you.

 

[jonathan92591]

Thanks again!