Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate data for previous 7 days
I am trying to sum the data for the previous week based on today's date.
The raw data is contained on one worksheet while i need the result on another. I am using the SUMIF formula and am able to get data for a specific date or all dates less than today or all dates greater than today. Here is what I have: =(SUMIF('Weekly dates'!A1"AZ1,'<="&A5,Details!L7:N7)) I've tried using just = or using and I've tried to minus 7 but that gives me just the data for the day 7 days previous. I need all the dates for the Weekly dates is a spreadsheet with all the week ending dates &A5 is the cell where the TODAY formula is located Details!L7:N7 is the spreadsheet and cells that contain the data I need to sum. What is need is only the data in the previous week based on today's date. Any ideas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate data for previous 7 days
The easiest way is =SUMIF(...,<=X,...)-SUMIF(...,<=X-8,...)
Hope that makes sense. Sam "Kesbutler" wrote: I am trying to sum the data for the previous week based on today's date. The raw data is contained on one worksheet while i need the result on another. I am using the SUMIF formula and am able to get data for a specific date or all dates less than today or all dates greater than today. Here is what I have: =(SUMIF('Weekly dates'!A1"AZ1,'<="&A5,Details!L7:N7)) I've tried using just = or using and I've tried to minus 7 but that gives me just the data for the day 7 days previous. I need all the dates for the Weekly dates is a spreadsheet with all the week ending dates &A5 is the cell where the TODAY formula is located Details!L7:N7 is the spreadsheet and cells that contain the data I need to sum. What is need is only the data in the previous week based on today's date. Any ideas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate data for previous 7 days
Hi Sam,
I tired and still get zero data when there are dollars for that week. Here is what I ended up with for a formula. =SUMIF('weekly dates'!A1:AZ1,"<=x"&A5,Details!L7:N7)-SUMIF('weekly dates'!A1:AZ1,"<=x-8"&A5,Details!L7:N7) Here is some more details that might help. We track financial spend weekly but I need to be able to provide a report that will only contain the previous weeks data. So we show only one week at a time. On worksheet 1; I need to calulate only the previuos weeks data for a number of projects. The weekly data is entered onto worksheet 2. I want worksheet 1 to auto calculate the financial data from worksheet 2 and update to show only the previous week each week the current data is populated. Make sense? "Sam Wilson" wrote: The easiest way is =SUMIF(...,<=X,...)-SUMIF(...,<=X-8,...) Hope that makes sense. Sam "Kesbutler" wrote: I am trying to sum the data for the previous week based on today's date. The raw data is contained on one worksheet while i need the result on another. I am using the SUMIF formula and am able to get data for a specific date or all dates less than today or all dates greater than today. Here is what I have: =(SUMIF('Weekly dates'!A1"AZ1,'<="&A5,Details!L7:N7)) I've tried using just = or using and I've tried to minus 7 but that gives me just the data for the day 7 days previous. I need all the dates for the Weekly dates is a spreadsheet with all the week ending dates &A5 is the cell where the TODAY formula is located Details!L7:N7 is the spreadsheet and cells that contain the data I need to sum. What is need is only the data in the previous week based on today's date. Any ideas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate data for previous 7 days
Correction. I updated your suggestions to be correct (duh) and it appears to
be working now. Thanks! "Kesbutler" wrote: Hi Sam, I tired and still get zero data when there are dollars for that week. Here is what I ended up with for a formula. =SUMIF('weekly dates'!A1:AZ1,"<=x"&A5,Details!L7:N7)-SUMIF('weekly dates'!A1:AZ1,"<=x-8"&A5,Details!L7:N7) Here is some more details that might help. We track financial spend weekly but I need to be able to provide a report that will only contain the previous weeks data. So we show only one week at a time. On worksheet 1; I need to calulate only the previuos weeks data for a number of projects. The weekly data is entered onto worksheet 2. I want worksheet 1 to auto calculate the financial data from worksheet 2 and update to show only the previous week each week the current data is populated. Make sense? "Sam Wilson" wrote: The easiest way is =SUMIF(...,<=X,...)-SUMIF(...,<=X-8,...) Hope that makes sense. Sam "Kesbutler" wrote: I am trying to sum the data for the previous week based on today's date. The raw data is contained on one worksheet while i need the result on another. I am using the SUMIF formula and am able to get data for a specific date or all dates less than today or all dates greater than today. Here is what I have: =(SUMIF('Weekly dates'!A1"AZ1,'<="&A5,Details!L7:N7)) I've tried using just = or using and I've tried to minus 7 but that gives me just the data for the day 7 days previous. I need all the dates for the Weekly dates is a spreadsheet with all the week ending dates &A5 is the cell where the TODAY formula is located Details!L7:N7 is the spreadsheet and cells that contain the data I need to sum. What is need is only the data in the previous week based on today's date. Any ideas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically calculate the previous days total 'sales' | Excel Discussion (Misc queries) | |||
7 previous days average | Excel Worksheet Functions | |||
Copy old Data from web query while keeping previous days data | Excel Worksheet Functions | |||
25 days previous to today? | Excel Discussion (Misc queries) | |||
date functions for previous 60/90/180 days | Excel Worksheet Functions |