Automatically calculate the previous days total 'sales'

[PVANS]

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

 

[Mike H]

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

 

[Mike H]

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

 

[PVANS]

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

 

[Lars-Åke Aspelin]

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

 

[Mike H]

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]

Mike, Lars

Thank you so much. I really appreciate the help. Lars, the formula works
fantastically well.

Really appreciate it.

Regards,

PVANS