Row Count by Department
On my sheet, I have a Department column that is filled in on every row. There are about 1200 rows total. Some example departments are: Accounting, AP, AR, Benefits, etc.
In addition, every row has a Status, Start Date, and End Date column filled out too.
I have a dashboard for this sheet. I would like to add a bar chart that shows Remaining Tasks (i.e., rows) by Department. Meaning, any tasks that where Status <> 'Complete'.
I thought about adding summary formulas to the main sheet, but that seems tedious, especially if departments are added or changed later. I also tried creating a report, but couldn't figure out how to count by department in a summarized way to report on the dashboard.
What is the best way to accomplish what I'm trying to do?
Thank you!
Best Answer
-
- In your sheet, create a column that gives you the result 1 for any record that you want to count. Something like =IF(Status@row = "Complete", 0, 1). You can call that column Count.
- Create another sheet where we will use a SUMIFS. In that sheet, put a list of your departments in the Primary Column. Then another column your SUMIFS which will look like: =SUMIFS({Count}, {Dept}, [Primary Column]@row)
If you create a new department, you will need to add that department to the summary sheet.
If you change a department name you will need to change it in the summary sheet.
Use the summary sheet as the source for the bar chart.
Answers
-
- In your sheet, create a column that gives you the result 1 for any record that you want to count. Something like =IF(Status@row = "Complete", 0, 1). You can call that column Count.
- Create another sheet where we will use a SUMIFS. In that sheet, put a list of your departments in the Primary Column. Then another column your SUMIFS which will look like: =SUMIFS({Count}, {Dept}, [Primary Column]@row)
If you create a new department, you will need to add that department to the summary sheet.
If you change a department name you will need to change it in the summary sheet.
Use the summary sheet as the source for the bar chart.
-
Thank you! That worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!