COUNTIFS on specific days and net change over time from report

Justin Ramos

Job Issue Log.PNG

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!

Genevieve P.

Hi @Justin Ramos-Flynn

You can use TODAY as a reference data to create your range in a COUNTIFS.

For example, if you're looking for the "last" 7 days that in the "Date issue closed" column, try this:

=COUNTIFS({Date issue closed}, >= TODAY(-7))

Since there's no possibility that the date could be in the future, we only need the one criteria that the date that the row was close is greater than 7 days ago.

If you're on an Enterprise Plan, I would suggest turning on Work Insights for the underlying sheet. This will give you a quick snapshot in the right hand panel to show data week-over-week, using the historical data of the sheet. See: Use Work Insights to visualize your data

Cheers,

Genevieve