Summing periodic sales on a rolling basis

[JohnnStar]

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

[FSt1]

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(),A2>NOW()-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
>
>

[FSt1]

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(),A2>NOW()-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(),A2>NOW()-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
> >
> >