Calculating Cumulative Enrollments by Month

Courtney Coules

We are tracking unique enrollments by month, and we also want to track cumulative enrollments by month. So, if 15 enroll in Jan and 10 enroll in Feb, I want the formula to show 25 for Feb and each month will roll together to show growth over the year. This is what I have for Jan but am unsure how to add on month after month:

=COUNTIFS({Referral Status}, OR(@cell = "Enrollment & Reentry Paperwork Complete", @cell = "Enrollment Complete/Reentry Paperwork Incomplete", @cell = "< 6 Months Post-Release", @cell = "> 6 Months Post-Release"), {enrollment date}, AND(@cell > [P1 End Date]#, @cell < [P2 End Date]#), {enrollment date}, MONTH(@cell) = 1)

Jeff Reisman

@Courtney Coules I'm thinking that for each month, you make the criteria for the enrollment date to be greater than or equal to Jan 1 of the current year, and less than the first day of the next month.

So for February:

=COUNTIFS({Referral Status}, OR(@cell = "Enrollment & Reentry Paperwork Complete", @cell = "Enrollment Complete/Reentry Paperwork Incomplete", @cell = "< 6 Months Post-Release", @cell = "> 6 Months Post-Release"), {enrollment date}, AND(@cell >= DATE(2023, 1, 1), @cell < DATE(2023, 3, 1)))

Then for March, change the 3 in bold above to 4 for less than April 1, and so on.

Then for December, change the last @cell to: @cell <= DATE(2023, 12, 31)

Jeff Reisman

@Courtney Coules I'm thinking that for each month, you make the criteria for the enrollment date to be greater than or equal to Jan 1 of the current year, and less than the first day of the next month.

So for February:

=COUNTIFS({Referral Status}, OR(@cell = "Enrollment & Reentry Paperwork Complete", @cell = "Enrollment Complete/Reentry Paperwork Incomplete", @cell = "< 6 Months Post-Release", @cell = "> 6 Months Post-Release"), {enrollment date}, AND(@cell >= DATE(2023, 1, 1), @cell < DATE(2023, 3, 1)))

Then for March, change the 3 in bold above to 4 for less than April 1, and so on.

Then for December, change the last @cell to: @cell <= DATE(2023, 12, 31)

Courtney Coules

It's working! Thank you so much!