End a count after a date has been reached

Originally Posted by tkell

I need to know if there is a way to have the cell assigned for the weekly backlog to stop counting and retain the value at the end of that week? The back log number is live and changes daily.

Yes...
Need more information to be more helpful I think. An example would be best - do you just want to lock the values of the cell after you are done with it?

Well, I'm not really sure is in your cell you would like to lock. If there is a formula there, can you just copy/paste special (values) to lock the results of the formula?

Hope this helps what im trying to say,
This is an example of the formula that I use to count backlog items per customer. I have a column in my table that subtracts my due date from current day, when this ticks down to zero, and if my "completed" column is blank it then adds to my running back log number. I have a separate sheet setup for Weeks 1 through 52. in this sheet I have a column for each week that counts how many RFQ's come, how many are completed and my running back log for the weeks end. currently i have a SUM formula to add all the back log items. at the weeks end i have to remove the formula and manually type in the number so that it will show the count of back log at the end of that week. what i am trying to do is not have to manually enter the count every week. sometimes i do forget and then do not have an accurate count when i do remember enter it. is there a way to have the cell stop counting at a certain date to retain the value it has counted to that point.
Thankyou again for trying to help with this.
=COUNTIFS('Quote Log'!Q:Q,"",'Quote Log'!X:X,"0",'Quote Log'!C:C,"PATRIA",'Quote Log'!J:J,"X")

I tried adding an example of my weekly report of RFQ's, but couldnt add a file for some reason. I want to retain the value at the end of the each week. the back log is a live running total and will change from week to week. the objective is to not have to manually enter the number and to let the spreadsheet "Lock" (if you will) and maintain the value at the end of the week.

Ok, now we are getting somewhere.

You should be able to include the static due date in the 'Quote Log' (if it's not already there) - For purposes of this example, I'll assume the Due Date is shown in column Z.

I think you just would want to add a date to your countifs function:

=COUNTIFS('Quote Log'!Q:Q,"",'Quote Log'!X:X,"0",'Quote Log'!C:C,"PATRIA",'Quote Log'!J:J,"X",'Quote Log'!Z:Z,"<SomeMaxDateCellOnThisRow")

So, that would reference SomeMaxDateCellOnThisRow (which would be like the Saturday of the WEEK#) that countif refers to.

Make sense?