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
Check out the Formula Handbook template!
