Summarising Data
 

I have a sheet that has columns of data - with the date in the first column
and a value in the second column. Data is entered for each day.

On a seperate sheet I want to summarise this data to give a weekly total,
preferably by locating a date on the first sheet and then adding the value
for this day to the six values above.

Have looked at using LOOKUP and OFFSET, but I'm sure there's an easier way?

Summarising Data
 

On Aug 16, 1:52*pm, Murray wrote:
I have a sheet that has columns of data - with the date in the first column
and a value in the second column. Data is entered for each day.

On a seperate sheet I want to summarise this data to give a weekly total,
preferably by locating a date on the first sheet and then adding the value
for this day to the six values above.

Have looked at using LOOKUP and OFFSET, but I'm sure there's an easier way?


Take, for example, one week: dates in A1:A7, values in B1:B7. In
(hidden?) column C, in C7, put =SUM(A1:A7), copy down. Then do
VLOOKUP on rows A & C.

Summarising Data
 

Hi,

Well there'sanother way,whether it's easier or not I'll let you decide

=SUMIF(Sheet1!A1:A365,"<"&D1,Sheet1!B1:B365)-SUMIF(Sheet1!A1:A365,"<"&D1-6,Sheet1!B1:B365)

Mind the line wrap it's all one line.
Where D1 is the date and your data are on Sheet1 in Cols A & B
It will give the sum of Column B for a date equal to d1 and the 6 previous
days.

Mike

Mike

"Murray" wrote:

I have a sheet that has columns of data - with the date in the first column
and a value in the second column. Data is entered for each day.

On a seperate sheet I want to summarise this data to give a weekly total,
preferably by locating a date on the first sheet and then adding the value
for this day to the six values above.

Have looked at using LOOKUP and OFFSET, but I'm sure there's an easier way?

Summarising Data
 

slight change

=SUMIF(Sheet1!A1:A365,"<="&D1,Sheet1!B1:B365)-SUMIF(Sheet1!A1:A365,"<"&D1-6,Sheet1!B1:B365)

Ensure it is formatted as general

Mike

"Mike H" wrote:

Hi,

Well there'sanother way,whether it's easier or not I'll let you decide

=SUMIF(Sheet1!A1:A365,"<"&D1,Sheet1!B1:B365)-SUMIF(Sheet1!A1:A365,"<"&D1-6,Sheet1!B1:B365)

Mind the line wrap it's all one line.
Where D1 is the date and your data are on Sheet1 in Cols A & B
It will give the sum of Column B for a date equal to d1 and the 6 previous
days.

Mike

Mike

"Murray" wrote:

I have a sheet that has columns of data - with the date in the first column
and a value in the second column. Data is entered for each day.

On a seperate sheet I want to summarise this data to give a weekly total,
preferably by locating a date on the first sheet and then adding the value
for this day to the six values above.

Have looked at using LOOKUP and OFFSET, but I'm sure there's an easier way?

Summarising Data
 

Thanks Mike,

Works a treat - exactly what I was looking for.

"Mike H" wrote:

slight change

=SUMIF(Sheet1!A1:A365,"<="&D1,Sheet1!B1:B365)-SUMIF(Sheet1!A1:A365,"<"&D1-6,Sheet1!B1:B365)

Ensure it is formatted as general

Mike

"Mike H" wrote:

Hi,

Well there'sanother way,whether it's easier or not I'll let you decide

=SUMIF(Sheet1!A1:A365,"<"&D1,Sheet1!B1:B365)-SUMIF(Sheet1!A1:A365,"<"&D1-6,Sheet1!B1:B365)

Mind the line wrap it's all one line.
Where D1 is the date and your data are on Sheet1 in Cols A & B
It will give the sum of Column B for a date equal to d1 and the 6 previous
days.

Mike

Mike

"Murray" wrote:

I have a sheet that has columns of data - with the date in the first column
and a value in the second column. Data is entered for each day.

On a seperate sheet I want to summarise this data to give a weekly total,
preferably by locating a date on the first sheet and then adding the value
for this day to the six values above.

Have looked at using LOOKUP and OFFSET, but I'm sure there's an easier way?

Summarising Data
 

Glad I could help

"Murray" wrote:

Thanks Mike,

Works a treat - exactly what I was looking for.

"Mike H" wrote:

slight change

=SUMIF(Sheet1!A1:A365,"<="&D1,Sheet1!B1:B365)-SUMIF(Sheet1!A1:A365,"<"&D1-6,Sheet1!B1:B365)

Ensure it is formatted as general

Mike

"Mike H" wrote:

Hi,

Well there'sanother way,whether it's easier or not I'll let you decide

=SUMIF(Sheet1!A1:A365,"<"&D1,Sheet1!B1:B365)-SUMIF(Sheet1!A1:A365,"<"&D1-6,Sheet1!B1:B365)

Mind the line wrap it's all one line.
Where D1 is the date and your data are on Sheet1 in Cols A & B
It will give the sum of Column B for a date equal to d1 and the 6 previous
days.

Mike

Mike

"Murray" wrote:

I have a sheet that has columns of data - with the date in the first column
and a value in the second column. Data is entered for each day.

On a seperate sheet I want to summarise this data to give a weekly total,
preferably by locating a date on the first sheet and then adding the value
for this day to the six values above.

Have looked at using LOOKUP and OFFSET, but I'm sure there's an easier way?