Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically calculate the previous days total 'sales'
Good morning, I hope someone can help.
I have a worksheet that has the following three columns: Date Reciept Price The worksheet is compiled automatically from information coming from the primary worksheet in the workbook. The date is generated each day using the =today() function. What I need to happen is at the start of a new day, a macro that totals all of the amounts in the previous days prices. I will give a mini view of what the worksheet looks like: Date Reciept Price 16/07/09 1 100 16/07/09 2 100 16/07/09 3 100 17/07/09 4 100 17/07/09 5 100 Therefore, I wish the formula to automatically add each days total prices together and show the answer in a new cell. I found that the SumIF formula will do a single days result, however I need this to happen every day without me needing to code a macro for each specific date. Thanks in advance for the help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically calculate the previous days total 'sales'
Hi,
I think you have a fundamental problem with the way you are deriving the date. If you use the today() function then 'tomorrow' the date will change to tomorrows date as it becomes 'today' I you overcome the date issue by (say) paste special paste values over the dates then this formula should do the sums for you =SUMPRODUCT((A1:A20=D1)*(B1:B20)*(C1:C20)) Where D1 is the date to summarise Mike "PVANS" wrote: Good morning, I hope someone can help. I have a worksheet that has the following three columns: Date Reciept Price The worksheet is compiled automatically from information coming from the primary worksheet in the workbook. The date is generated each day using the =today() function. What I need to happen is at the start of a new day, a macro that totals all of the amounts in the previous days prices. I will give a mini view of what the worksheet looks like: Date Reciept Price 16/07/09 1 100 16/07/09 2 100 16/07/09 3 100 17/07/09 4 100 17/07/09 5 100 Therefore, I wish the formula to automatically add each days total prices together and show the answer in a new cell. I found that the SumIF formula will do a single days result, however I need this to happen every day without me needing to code a macro for each specific date. Thanks in advance for the help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically calculate the previous days total 'sales'
If you always want to summarise yesterdays data then this formula is probably
better =SUMPRODUCT((A1:A20=TODAY()-1)*(B1:B20)*(C1:C20)) The issue with the dates in your columns remains an issue. Mike "Mike H" wrote: Hi, I think you have a fundamental problem with the way you are deriving the date. If you use the today() function then 'tomorrow' the date will change to tomorrows date as it becomes 'today' I you overcome the date issue by (say) paste special paste values over the dates then this formula should do the sums for you =SUMPRODUCT((A1:A20=D1)*(B1:B20)*(C1:C20)) Where D1 is the date to summarise Mike "PVANS" wrote: Good morning, I hope someone can help. I have a worksheet that has the following three columns: Date Reciept Price The worksheet is compiled automatically from information coming from the primary worksheet in the workbook. The date is generated each day using the =today() function. What I need to happen is at the start of a new day, a macro that totals all of the amounts in the previous days prices. I will give a mini view of what the worksheet looks like: Date Reciept Price 16/07/09 1 100 16/07/09 2 100 16/07/09 3 100 17/07/09 4 100 17/07/09 5 100 Therefore, I wish the formula to automatically add each days total prices together and show the answer in a new cell. I found that the SumIF formula will do a single days result, however I need this to happen every day without me needing to code a macro for each specific date. Thanks in advance for the help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically calculate the previous days total 'sales'
Hi there Mike,
Thanks for the quick reply. When I say I am using the =today() formula, what I mean is that, in worksheet 1 there is a template of the reciept that I would like to use. Once the reciept has been completed, I press a button that automatically updates the client accounts with the information from the reciept. Therefore, the =today() formula is used only in the reciept template thus ensuring that as each day starts, the correct date is on the reciept, but in terms of the subsequent client accounts that have the view that I showed initially, any information is simply called from the reciept. In your reply you suggest I overcome the date issue that you described by pasting special values, could you please clarify. Sorry if I am not fully understanding you, to be honest this is by far the most technical workbook I have created. Thanks again "Mike H" wrote: Hi, I think you have a fundamental problem with the way you are deriving the date. If you use the today() function then 'tomorrow' the date will change to tomorrows date as it becomes 'today' I you overcome the date issue by (say) paste special paste values over the dates then this formula should do the sums for you =SUMPRODUCT((A1:A20=D1)*(B1:B20)*(C1:C20)) Where D1 is the date to summarise Mike "PVANS" wrote: Good morning, I hope someone can help. I have a worksheet that has the following three columns: Date Reciept Price The worksheet is compiled automatically from information coming from the primary worksheet in the workbook. The date is generated each day using the =today() function. What I need to happen is at the start of a new day, a macro that totals all of the amounts in the previous days prices. I will give a mini view of what the worksheet looks like: Date Reciept Price 16/07/09 1 100 16/07/09 2 100 16/07/09 3 100 17/07/09 4 100 17/07/09 5 100 Therefore, I wish the formula to automatically add each days total prices together and show the answer in a new cell. I found that the SumIF formula will do a single days result, however I need this to happen every day without me needing to code a macro for each specific date. Thanks in advance for the help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically calculate the previous days total 'sales'
On Fri, 17 Jul 2009 02:08:16 -0700, PVANS
wrote: Good morning, I hope someone can help. I have a worksheet that has the following three columns: Date Reciept Price The worksheet is compiled automatically from information coming from the primary worksheet in the workbook. The date is generated each day using the =today() function. What I need to happen is at the start of a new day, a macro that totals all of the amounts in the previous days prices. I will give a mini view of what the worksheet looks like: Date Reciept Price 16/07/09 1 100 16/07/09 2 100 16/07/09 3 100 17/07/09 4 100 17/07/09 5 100 Therefore, I wish the formula to automatically add each days total prices together and show the answer in a new cell. I found that the SumIF formula will do a single days result, however I need this to happen every day without me needing to code a macro for each specific date. Thanks in advance for the help If your data is in column A to C starting on row 2 (with the headers on row 1) try the following formula in cell D2: =IF(A2<A3,SUMPRODUCT((C$2:C$1000)*(A$2:A$1000=A2) ),"") Copy the formula down as far as you (will) have data in columns A to C Change the 1000 to fit your maximum number of data rows as well. The result in column D will be a daily sum next to the last entry for each specific date. Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically calculate the previous days total 'sales'
Hi,
If you have a date (or in fact any value) derived from a formula then it is lable to change when the sheet recalculates. If you want to prevent this and keep the date static then you can do this:- Select the cells Edit - Copy Edit - paste special - paste values. Mike "PVANS" wrote: Hi there Mike, Thanks for the quick reply. When I say I am using the =today() formula, what I mean is that, in worksheet 1 there is a template of the reciept that I would like to use. Once the reciept has been completed, I press a button that automatically updates the client accounts with the information from the reciept. Therefore, the =today() formula is used only in the reciept template thus ensuring that as each day starts, the correct date is on the reciept, but in terms of the subsequent client accounts that have the view that I showed initially, any information is simply called from the reciept. In your reply you suggest I overcome the date issue that you described by pasting special values, could you please clarify. Sorry if I am not fully understanding you, to be honest this is by far the most technical workbook I have created. Thanks again "Mike H" wrote: Hi, I think you have a fundamental problem with the way you are deriving the date. If you use the today() function then 'tomorrow' the date will change to tomorrows date as it becomes 'today' I you overcome the date issue by (say) paste special paste values over the dates then this formula should do the sums for you =SUMPRODUCT((A1:A20=D1)*(B1:B20)*(C1:C20)) Where D1 is the date to summarise Mike "PVANS" wrote: Good morning, I hope someone can help. I have a worksheet that has the following three columns: Date Reciept Price The worksheet is compiled automatically from information coming from the primary worksheet in the workbook. The date is generated each day using the =today() function. What I need to happen is at the start of a new day, a macro that totals all of the amounts in the previous days prices. I will give a mini view of what the worksheet looks like: Date Reciept Price 16/07/09 1 100 16/07/09 2 100 16/07/09 3 100 17/07/09 4 100 17/07/09 5 100 Therefore, I wish the formula to automatically add each days total prices together and show the answer in a new cell. I found that the SumIF formula will do a single days result, however I need this to happen every day without me needing to code a macro for each specific date. Thanks in advance for the help |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically calculate the previous days total 'sales'
Mike, Lars
Thank you so much. I really appreciate the help. Lars, the formula works fantastically well. Really appreciate it. Regards, PVANS "Lars-Ã…ke Aspelin" wrote: On Fri, 17 Jul 2009 02:08:16 -0700, PVANS wrote: Good morning, I hope someone can help. I have a worksheet that has the following three columns: Date Reciept Price The worksheet is compiled automatically from information coming from the primary worksheet in the workbook. The date is generated each day using the =today() function. What I need to happen is at the start of a new day, a macro that totals all of the amounts in the previous days prices. I will give a mini view of what the worksheet looks like: Date Reciept Price 16/07/09 1 100 16/07/09 2 100 16/07/09 3 100 17/07/09 4 100 17/07/09 5 100 Therefore, I wish the formula to automatically add each days total prices together and show the answer in a new cell. I found that the SumIF formula will do a single days result, however I need this to happen every day without me needing to code a macro for each specific date. Thanks in advance for the help If your data is in column A to C starting on row 2 (with the headers on row 1) try the following formula in cell D2: =IF(A2<A3,SUMPRODUCT((C$2:C$1000)*(A$2:A$1000=A2) ),"") Copy the formula down as far as you (will) have data in columns A to C Change the 1000 to fit your maximum number of data rows as well. The result in column D will be a daily sum next to the last entry for each specific date. Hope this helps / Lars-Ã…ke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I take a sub total field and calculate sales tax to it | New Users to Excel | |||
Calculate Total Sales from a Database | Excel Worksheet Functions | |||
formula to calculate sales tax from total sales | Excel Worksheet Functions | |||
How do I calculate a MTD total - yesterdays sales in Excel? | Excel Worksheet Functions | |||
How to sum total automatically in more than one sales invoices | Excel Worksheet Functions |