Status Complete Chart

Joshua Medina

I am trying to create a chart on a dashboard indicating percent completed. Essentially I have a project where 2 companies need to complete a task for each location. What I have:

-A master sheet of all the companies and their locations

-A form to enter data

-A lookup to the master sheet from the form data indicating if that location is complete or not

I can't figure out how to represent this in a chart. This would preferably be a pie for each company using the master sheet of "Complete" and "Not Complete". Any thoughts or other methods to do this?

Genevieve P.

Hi Joshua,

Charts require numeric data so you would want to use a formula to calculate the number of "Complete" statuses and "Not Complete" statuses.

You'd likely want to set up a little chart area at the top of your Master Sheet to create this calculation per-company... maybe something like this? Of course, it will depend on what your master sheet looks like. I've coloured the background of the cells just to make it easier to identify as a Summary Table.

Then you'll see I have a COUNTIFS formula adding up the number based on two criteria: the Company Name and if it's Complete or not.

=COUNTIFS([Company Name]:[Company Name], "Company 1", [Complete/Not?]:[Complete/Not?], "Complete")

You can then select just the top two rows for one chart, and the next two rows for your second chart. Does that make sense? Here are some Help Center articles I used to help with this: COUNTIFS Function / Referencing Columns / Chart Widgets / Webinar on Preparing Data

If the formula above doesn't work, it may be helpful to see a screen capture of your master sheet. We could also do a cross-sheet formula instead and have the table in its own Chart Sheet, but again, it would be helpful to see how your master sheet is set up (please block out any sensitive data if you do post a picture).

Let me know if you have any questions about this!

Cheers,

Genevieve