Stop calculating when a date is reached

[Lefty099]

Hi guys. I am trying to find a formula that will sum sales up to a specified date, at which point it stops summing.
eg. I have 4 rows dated 01/02/16, 08/02/16, 15/02/16, 22/02/16 and so on.
If those rows are in column B I want the relevant row in Column C to sum up the figures that are updated daily up until the date specified. eg. $10000 up until the 08/02/16.
If we sell another $4000 on the 09/02/16, I need the row dated 15/08/2016 to be $4000 and the previous line to stay at $10000.
There is a master cell at the top which would read total monthly sales, being $14000 at this point.
Brief.
A1 = Running Total of sales.
A2 = 08/02/16 B2= Sales up to A2
A3 = 15/02/16 B3 = Sales Between 08/02/16 and 15/02/16
A4 = 22/02/16 B4 = Sales Between 15/02/16 and 22/02/16

I hope this makes sense.

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Where are your sales dates and sales values? It's not clear from your description, at least to me.

 

[Lefty099]

Hi there. The sales dates Run in 7 day lots down column A1,A2,A3. The sales Value is imported from another sheet into E1 and increases daily based on previous days sales. This figure is a "Total sales for the month report, not daily sales by date. This is why I need to be able to set dates where it stops tallying sales and moving into the next Row.

A B
1 TOTAL SALES FOR MONTH $$ =TODAY()
2 08/FEB/2016 Sales between 1st of Feb and A2 (Need this number to lock in place once date passes)
3 15/Feb/2016 Sales between A2 and A3 (Need this number to lock in place once date passes)
4 22/Feb/2016 Sales Between A3 and A4 (Need this number to lock in place once date passes)
5

I have tried =IF(A2=B1,A1,0) which works fine until B2 ticks over another day, then A2 goes back to 0.

Hope this helps

 

[Lefty099]

Column A has total sales and dates, Column B is where the Sales Between should be.

 

[Andrew Poulsom]

Sorry, I still don't understand. Don't you have a list of sales and their dates somewhere?

 

[Lefty099]

The report is a "total month to date sales", the report i am using just shows the totals as there is other info on that report i need which is why it has to be in that format.

 

[Andrew Poulsom]

I don't know how to do it without having a list of daily sales to sum, sorry.

 

[Matty]

Hi,

To ensure would-be helpers are clear about what's needed, please post example data using the following (or similar) HTML maker:

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Thanks,

Matty

 

[Lefty099]

Hi again Guys and gals. I have reran a different report which now includes dates as well as all the other info i need. So I will try one more time to hopefully clear this up for you all. Thanks for your patience.

Data I need on Report Sheet. Column D= Date, Column I= salesperson 9 (eg. Bill.Jones (5555), Column T = Sales.

On My main page.
A1 = TODAY()
B1 = =TODAY()-DAY(TODAY())+1 Gives me the first day of the month.
B13 = B2+7
B14 = B13+7
B15 = B14+7
Column H = Salesperson Displayed as Bill.Jones (5555), same as I on report sheet.

What I need to do is calculate sales for salesperson 5555 between the 1st of the month and B13. Then calculate sales for 5555 between B13 and B14, then sales for 5555 between B14 and B15 etc.

Hope this helps a bit.

Thanks again.

 

[Matty]

As per my previous post, could you provide your example via a HTML maker. This will then allow us to quickly work on possible solutions.

If you could do this and include the expected result, I'm sure you will receive a quick response.

Matty