How to automate monthly metric?

Carroll Wall

My customer is looking for a month over month metric for a dashboard

Essentially, how many rows out of the total have a check mark in a certain column at the start of each month.

They want to be able to see the trends over time, so I need to show all the months as time passes.

Counting the check marks with a =COUNT formula in a sheet summary is easy to set up but I cannot figure out how to automatically capture the count each month without over-writing the previous number or going in on Month Day 1 and hand entering that number into a chart so it updates the dashboard widget.

Any suggestions would be sincerely appreciated!

Thank you!

Carroll

Paul Newcome

I would suggest moving the count formula to a separate sheet. Then set up a copy row automation to copy this count over at (for example) 11:00pm on the last day of every month. This will copy the row over to another sheet which captures the static data.

Carroll Wall

Thanks Paul for the speedy reply!

Can you help me understand how to avoid over writing the previous month's data each time?

Paul Newcome

You can't stop the formula from overwriting. That's why at the end of every month you would use the Copy Row automation to capture the static data in a separate sheet. Each month will add a new row of static data when the copy row automation runs.

Carroll Wall

Do you envision 3 sheets?

1. Original data set sheet
2. A second sheet containing a formula to count the check marks from the original sheet which will monthly export a row to
3. A third sheet from which I can gather data for my widget

Or am I misunderstanding?

Paul Newcome

That is correct.