Need a formula to update info by date but also maintain old info by date

[haganator]

Greetings everyone,


I'm trying to write a formula to check for new data added each day with certain criteria. One worksheet has data added to it, and the other worksheet runs the calculations. So Worksheet 1 will have x number of lines of new data added to the top of the list, and each new line has the date in Column C and specific criteria in Column G. All lines below these will have data from previous days.


On worksheet 2, I will go to the bottom of the list, highlight the last row, which contains yesterday's date, and drag down a new row/date. I need a formula in these rows to count the number of instances where today's date and criteria in Column G are met to update a count. This is my current formula:


COUNTIFS('Total Prov orig'!C:C,TODAY()-0,'Total Prov orig'!G:G,"SLS-BU1023")


This works, however, I have to manually update the "TODAY()-0" to "TODAY()-1, to -2 and so on to account for previous days worth of data. How do I automate this formula such that when I add a new row, it will update the formula for all previous rows?


Thanks in advance,


Jeff

 

[MNGuy183]

You can have the date in a column and then just reference the cell with the date in it rather than the TODAY function.