Aggregating Data until a certain date

[mz1023]

Hello!

I have a spreadsheet where we are entering youth that have com in for counseling. We have to report out on the number of youth served at different points in the fiscal year. I have a column for their admission date and one for their release date I am looking to count up how many students were active at the beginning of a quarter. For example I want to know how many students have an admission date but no release date on July 1st. But, as far as I know for excel later in the year after July if someone puts in a release date for that student then the numbers will change. I would like it to be so that on July 1st it takes the number it counted and freeze it there so at any point in a year you can look at it. As of right now it would change if a release date is put in because a release date means the case is no longer open. Here is what I have for example in the one cell right now:

=COUNTIFS('Subsidy Grant Master'!L:L, "White", 'Subsidy Grant Master'!K:K, "Male", 'Subsidy Grant Master'!T:T, "<=7/1/2021", 'Subsidy Grant Master'!V:V, {">=7/1/2021","<=9/30/2021"})

Please let me know if you need more information. I will be checking this a lot to see answers so I should be pretty quick at responding.

 

[mumps]

I'm not sure if that can be done with a formula (formulas are not my strong point). You may need a macro. If you are open to using a macro, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).