COUNTIFS on specific days and net change over time from report
Hello all, I am struggling on finding a solution for this. From our calendar, when we have issues, we log the issue in the notes section, categorize the main issue, and change the "Incomplete Job" column to Job down or go back. When the "Incomplete Job" changes, I have an automation to record the date in the column, "Date Incomplete Job Marked". This moves it to a "Job Issue Report" that my team looks at attached. We track these jobs so we can get back to complete the job and when it is completed, we mark the "Close Issue" checkbox column which takes it off the report and records a date in a hidden column called, "Date issue closed".
As you can see from the picture, we currently have 49 issues today but my boss wants to see the number of issues over time, the number of issues closed, and a net change every day. For example, we could have 5 jobs with issues today and it changes to 54 then close 3 to get a net of 51 but there's no way to know what had yesterday to compare against and if we are improving or it's the same number of issues everyday.
Anyways, I am struggling to come up with a COUNTIFS formula to track the number of issues still open or resolved from a given date (ex. last 7 days) since it is a dynamic report. I'm thinking I'll need a countifs with a range and criteria but not sure how to know how many were issues on a specific day. Thank you!
Help Article Resources
Check out the Formula Handbook template!