negative inventory by Day, keep previous day's totals

**swiftrain** · 11-26-2012, 12:43 PM

I pull information from another program and it places it as-is in "sheet1". This then populates "Sales". It then sorts by Column G and I total the absolutes into the correct date of the calendar.

I would like to put a formula in the calendar to sum each group on a daily basis, but keep the previous day's totals as well. I cannot seem to figure this out. the formula I have in L16 is =SUMPRODUCT(($G$2:$G$1000=1)*(L15=$J$1)*$F$2:$F$1000) but it disappears as the day changes.

I have attacheda mock spreadsheet for an example. (all propriety information has been removed)

Thank youinventory Test.xls

**TheExcelphile** · 11-26-2012, 02:31 PM

Hi swiftrain!
Welcome to the forum!

What you are asking for can't be done using formulas.

The way I see it, you have only two options.
First, keep your sheet the way it is, and Copy->Paste Special (Paste Values) for each date.
Else, insert one more column with dates for your data, and use the formulas I have used.

If you decide to go with the second option, make sure to include all of your raw data for the full month. Don't delete the previous day's data when you import new data. Just keep adding new data at the bottom of the old one in columns A:G and put in the date in column H. Use the formulas I've put in M16:M18. This way, you'll get the result you want.

Don't forget to click on the little star to the left of this post if you feel I helped!