Formula that will track the total number of jobs on the sheet at the end of each month

William Cook

Hello,

I am looking to create a formula that will track the total number of jobs (populated rows) in the sheet at the end of each month. The data outputted based on the formula will be displayed on a line graph widget in order to track the progress of the company by each month. For example, if there are 115 lines at the end of January then ideally the formula would output the total number of jobs on the sheet and display it in the line graph widget. After that, it would be great if there was a way to keep the data from previous months visible to see if the pace of completed jobs are either going up (less lines total at each month end) or down (more lines total at each month end).

Additionally, I would also like for it to track just on a 12-month basis, so for instance, once December 31st arrives, it will continue to display data from the previous 12 months regardless of a new year starting.

Any help would be tremendously appreciated! Thank you all for your time in reading this question. My apologies that it was pretty wordy!

Adam Murphy

Hi @William Cook,

I would add a column and use a simple COUNT formula to count the number of rows in the sheet. This will conintuously change as rows are added removed. I would add a second column for the "Move date".

I would then create a helper sheet and add an automation to existing sheet that runs on the last day of the month and 1/stamps a date in the Move Date column and 2/copies a row to the helper sheet (numerous ways to identify a single row to copy).

This will give you one entry per month in the helper sheet, with each entry containing the row count at the end of that given month. Use those 2 fields in the helper sheet to create your chart.

Would this work?

William Cook

@Adam Murphy Thank you for the insight! I will try my best to replicate your recommendation with the data I have in front of me. Currently, I have a job summary sheet that is tied to a dashboard and displays new jobs by day, total complete jobs, total open jobs, total jobs (to include a total count of jobs "in progress") and some others as well.

Would this be something I could implement into my summary sheet? If so, how would you structure the formula based on the number of rows in the sheet? The good thing is that the dates that the jobs were completed is irrelevant in this scenario because the only thing my manager wants added is a line chart that displays the "Total Jobs" (rows) at the end of each month. We also want these recordings to remain on the line graph to see the history of previous months totals. Let me know if you have some suggestions for formula structures.

Thank you so much for your help Adam!