Row Count by Department

TJ Norris

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!

James Keuning

1. 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.
2. 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.

James Keuning

1. 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.
2. 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.

TJ Norris

Thank you! That worked!