Count Report

jccarley

Hello,

I have a sheet with 7 columns each indicating a different team or step in our process (UX, Content, Regulatory Review, Planning, Execution, Data, Testing).

I also have around 300 rows of tasks that need to hit each team/step.

All cells in the sheet begin at "Not Started" As they move through each team, the team updates the status of that task in our sheet under their column. (Statuses: Not Started, In Progress, Needs Review, Complete, Not Applicable).

I want to have a place (maybe in a dashboard) where we can see how many tasks are in each status. We want one count of statuses for full sheet/all teams and anther count for each team so we would count the statuses in their row.

Can anyone assist with how we would do this?

Mark.poole

@jccarley

Instead of a row report. Create another sheet. Referencing your original sheet.

In the primary column have each row be the Over all total and name of your departments. Then create columns for each status.

At which point you can reference the original sheet with a formula similar to

=COUNTIF({ref Column}, "Not Started")

You could end up with something simular to this.

jccarley

Thank you to whomever can help. Desperately trying to figure this out. 😀

Mark.poole

To me the easiest way to achieve this is to build a report. That can be pulled into a dashboard. You could also do a summary on the sheet with formulas to pull what you need from each column. Then on the dashboard you could pull the summary widget.

Paul Newcome

I would suggest a helper column with a nested IF to output which step each row is in. Something along the lines of

=IF([First Step]@row = "", "First Step", IF([Second Step]@row = "", "Second Step", ………………

jdunn

Yes, you can do this using a Report. Make a report referencing your sheet, group by "Status" and summarize by "Count". You'll get a line much like this one from my report: In my case I am Using a "Module Name" instead of a status, but the concept is the same.
You can then add the report to a dashboard as a widget.

jccarley

Thank you both.

What would you recommend if my end goal is to have an easy visual for my manager showing:

Total Overall Number of Items (all cells on sheet in the 7 rows)

Total Number of Items In each category across all the columns (Total number "complete" on whole sheet)

Total Number of Items in each category per step (Total of each status in the Testing Column).

He basically wants to know where we stand and what percent complete we are at any given time.

I will post a screenshot of a section of the sheet.

jccarley

jccarley

I have tried creating a row report and a sheet summary report and neither seems to get me where I want to be.

Any suggestions?

jccarley

I meant to like a comment and I accidently hit, "Yes, this answered the question" Is my question still active? I could still use the help. You all are amazing to be here helping newbies like me.

Mark.poole

@jccarley

Instead of a row report. Create another sheet. Referencing your original sheet.

In the primary column have each row be the Over all total and name of your departments. Then create columns for each status.

At which point you can reference the original sheet with a formula similar to

=COUNTIF({ref Column}, "Not Started")

You could end up with something simular to this.

jccarley

thank you to all!!

Mark, I tried your method and that worked well. I was able to create this dashboard from that sheet. Perfect solution. Much appreciated.