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

haganator

New Member
Joined
Aug 5, 2008
Messages
42
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

MNGuy183

New Member
Joined
Apr 26, 2016
Messages
14
You can have the date in a column and then just reference the cell with the date in it rather than the TODAY function.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,451
Messages
5,601,727
Members
414,470
Latest member
glukemey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top