Automating count formula and trend summary

Jennifer Valeros

Hello All,

I am wondering if there is a way to automate this process. I am trying to get a count of each line item status type at the end of each day. Currently, I am updating a master sheet that has a status column reading either “active” or “not active.”

At the end of each day, I am manually totaling the number of active lines and inputting it into a separate sheet which feeds into a trend summary (line graph in picture). Each data point is the final count at the end of each day.

I am looking for a way to automatically count the total active cases at the end of each day. That can automatically feed into the trend summary.

Is this doable without 3rd party software? How does this need to be set up?

Thank you!

Matt Johnson

Hi @Jennifer Valeros

It sounds like you need a COUNTIFS formula on the sheet that feeds the trend summary. You can set up the formula as cross sheet reference which will count things from the master sheet and put the counts on the feeder sheet. If you could share some screen shots of the master sheet, I could help with the formula.

I'm thinking something like IF the date is X, then count all "active" items in this column and "not active" items into this other column. Something along those lines should work.

Paul Newcome

I would suggest setting up two more sheets. One that captures the live data and has an automation to copy rows to the second sheet on a daily basis to capture the static data. This second sheet with the static data would be what you build your chart off of.

Jennifer Valeros

@Paul Newcome Thanks Paul. I've tried something similar, but once the data changes from the copy, the data point on the trend line changes with it. Is there a way to 'unlink' the sheets on a daily basis so that it shows as a separate data point?

@Matt Johnson Thank you Matt. I already have a stacked bar graph that uses a 'countifs' formula for this purpose. I'm really just trying to automate the way data is gathered for the trend.

Paul Newcome

If you include a column on the live count sheet with today's date on each row, when it copies the rows over to the copy sheet, it will capture each date. The data on the copy sheet is static, and each day new rows will be added with the daily count.

Each day will be a separate data point on the copy sheet.