| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summarising data into a table | Excel Worksheet Functions | |||
| summarising page data | Excel Discussion (Misc queries) | |||
| Ways of Summarising data | Excel Discussion (Misc queries) | |||
| Summarising a List of Data | Excel Discussion (Misc queries) | |||
| Summarising data from several tables | Excel Worksheet Functions | |||
Linear Mode
