Summarising Data

[Murray]

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?

 

[robzrob]

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.

 

[Mike H]

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

 

[Mike H]

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

 

[Murray]

Thanks Mike,

Works a treat - exactly what I was looking for.

 

[Mike H]

Glad I could help

Thanks Mike,

Works a treat - exactly what I was looking for.