Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Automating count formula and trend summary
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!
Answers
-
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.
-
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.
-
@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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
