Summing periodic sales on a rolling basis
 

Hello All,

Had a look in the tips for this one, thought it might be to do with
dynamic range summing, but still can't work it out

I have a sheet detailing auction sales. Some days have more than one
auction sale, other days have no auction sales.

I want to have a cell somewhere on the sheet that gives me a total
sales value for the last 7 days (inclusive) and continually updates as
more rows are added.

E.g., in the attached list I would like to create a formula that gives
me the sales made between 12 July and 18 July and then when I add data
for 19 July I would like the calc cell to change giving me the value
from 13 July to 19 July automatically; even though there were no sales
on 13 July I need the formula to recognise that it should only sum to a
13 July value and if there isn't a value it should not add the previous
12 July value instead.

I have tried using lookup formulas but because the dates aren't unique
it only returns the sales value of the first date that it comes to, not
the value of any other sales achieved on the same day.

Any suggestions gratefully received.

Cheers, folks.

JS

Column A Column B
Date Sale Value
10-Jul-06 £50
10-Jul-06 £60
11-Jul-06 £65
12-Jul-06 £40
14-Jul-06 £35
14-Jul-06 £45
16-Jul-06 £56
16-Jul-06 £42
17-Jul-06 £55
18-Jul-06 £62


--
JohnnStar
------------------------------------------------------------------------
JohnnStar's Profile: http://www.excelforum.com/member.php...o&userid=36619
View this thread: http://www.excelforum.com/showthread...hreadid=563648

Summing periodic sales on a rolling basis
 

hi,
you might acheive this if you use 2 formulas.
assuming dates are in column a, values in column b then in column c add this
formula....
=IF(AND(A2<NOW(),A2NOW()-7),1,0) and copy down for all rows.
then in your cal cell add this formula
=SUM(C2:C1000) or however many rows you need.

regards
FSt1

"JohnnStar" wrote:


Hello All,

Had a look in the tips for this one, thought it might be to do with
dynamic range summing, but still can't work it out

I have a sheet detailing auction sales. Some days have more than one
auction sale, other days have no auction sales.

I want to have a cell somewhere on the sheet that gives me a total
sales value for the last 7 days (inclusive) and continually updates as
more rows are added.

E.g., in the attached list I would like to create a formula that gives
me the sales made between 12 July and 18 July and then when I add data
for 19 July I would like the calc cell to change giving me the value
from 13 July to 19 July automatically; even though there were no sales
on 13 July I need the formula to recognise that it should only sum to a
13 July value and if there isn't a value it should not add the previous
12 July value instead.

I have tried using lookup formulas but because the dates aren't unique
it only returns the sales value of the first date that it comes to, not
the value of any other sales achieved on the same day.

Any suggestions gratefully received.

Cheers, folks.

JS

Column A Column B
Date Sale Value
10-Jul-06 £50
10-Jul-06 £60
11-Jul-06 £65
12-Jul-06 £40
14-Jul-06 £35
14-Jul-06 £45
16-Jul-06 £56
16-Jul-06 £42
17-Jul-06 £55
18-Jul-06 £62


--
JohnnStar
------------------------------------------------------------------------
JohnnStar's Profile: http://www.excelforum.com/member.php...o&userid=36619
View this thread: http://www.excelforum.com/showthread...hreadid=563648

Summing periodic sales on a rolling basis
 

hi again,
opps. should have read your post more carefully. you want to sum the saves
values not count sales. you can use the same procedure as below just change
the if formula.
=IF(AND(A2<NOW(),A2NOW()-7),A2,0) and copy down dor all rows.
the sum formula in the cal cell will be the same.
sorry bout that.
regards
FSt1

"FSt1" wrote:

hi,
you might acheive this if you use 2 formulas.
assuming dates are in column a, values in column b then in column c add this
formula....
=IF(AND(A2<NOW(),A2NOW()-7),1,0) and copy down for all rows.
then in your cal cell add this formula
=SUM(C2:C1000) or however many rows you need.

regards
FSt1

"JohnnStar" wrote:


Hello All,

Had a look in the tips for this one, thought it might be to do with
dynamic range summing, but still can't work it out

I have a sheet detailing auction sales. Some days have more than one
auction sale, other days have no auction sales.

I want to have a cell somewhere on the sheet that gives me a total
sales value for the last 7 days (inclusive) and continually updates as
more rows are added.

E.g., in the attached list I would like to create a formula that gives
me the sales made between 12 July and 18 July and then when I add data
for 19 July I would like the calc cell to change giving me the value
from 13 July to 19 July automatically; even though there were no sales
on 13 July I need the formula to recognise that it should only sum to a
13 July value and if there isn't a value it should not add the previous
12 July value instead.

I have tried using lookup formulas but because the dates aren't unique
it only returns the sales value of the first date that it comes to, not
the value of any other sales achieved on the same day.

Any suggestions gratefully received.

Cheers, folks.

JS

Column A Column B
Date Sale Value
10-Jul-06 £50
10-Jul-06 £60
11-Jul-06 £65
12-Jul-06 £40
14-Jul-06 £35
14-Jul-06 £45
16-Jul-06 £56
16-Jul-06 £42
17-Jul-06 £55
18-Jul-06 £62


--
JohnnStar
------------------------------------------------------------------------
JohnnStar's Profile: http://www.excelforum.com/member.php...o&userid=36619
View this thread: http://www.excelforum.com/showthread...hreadid=563648