How to have Percent Complete for two columns?

GDHernandez

Hello,

I'm using the “New Hire Onboarding Template” that my team has expanded on. We added another checkbox column, and I'm hoping to have it be included in the % Complete section as well.

Below you'll see the current formula that “came with” the template. How could I add the “Delivered” column to be included in this? I tried replacing the second “Ordered” in each section with “Delivered” but that gave me an error.

Any help is appreciated, thank you!

2023-03-08_13-37-09.png

Paul Newcome

How would it affect the % Complete?

Note: The existing formula is more than it really needs to be. Generally speaking, to get the % of boxes checked, you would count how many are checked and divide that by the total number of boxes. It can be very simplified like so:

=COUNTIFS(Ordered:Ordered, 1) / COUNTIFS(Task:Task, @cell <> "")

GDHernandez

Thanks for the quick response, Paul.

When I click on a checkbox, it adds to the “% Complete”. I'm hoping to do the same with the new column I've added, labeled “Delivered”.

I'm also encountering an issue when using a filter to remove rows, the % Complete won't tally up to 100%. Is there a way to correct that? Or is that simply the result of hiding boxes via a filter?

Thanks again.

Paul Newcome

My apologies. I should have worded my question differently.

Will you have just a single % Complete for both columns combined, or are you going to have two separate % Completes?

Basically... If I have 10 rows and they are all checked for Ordered but none are checked for delivered, would that be 50% or would it be 100% and 0%?

GDHernandez

I'm hoping to have both columns tally up to 100% in a single percent complete box, if at all possible.

In your scenario, ideally it would be 50%. The "Delivered" column would be the other 50%.

Sorry if this doesn't make sense, but I appreciate the help!

Paul Newcome

It does make sense. Give this a try...

=COUNTIFS(Ordered:Delivered, @cell = 1) / COUNTIFS(Ordered:Delivered, OR(@cell = 1, @cell <> 1))

GDHernandez

Thank you! It does still read as "Unparseable" when I enter that.

Genevieve P.

@GDHernandez

Can you post a screen capture with the formula open in a cell so we can take a look?

GDHernandez

Paul Newcome

Its like it isn't registering the Ordered and/or Delivered columns. I don't see them in the latest screenshots either. Did you happen to change the column names? It looks like maybe the one was changed to Done?

GDHernandez

It was user error on the #UNPARSEABLE, sorry about that! And thank you for your help!

Paul Newcome

Happy to help. 👍️

GDHernandez

One last question,

Is it possible to have this % complete box check specific rows only? It looks like it's using all cells in the two columns, and some I have "removed" checkboxes from as they are not needed.

Thank you.

Paul Newcome

Exactly which method did you use to "remove" the checkboxes?

GDHernandez

I just typed "," in each check box I didn't want to appear and changed the font color so it "blends". I was searching the Community forums for ways to remove them and was unable to find any.

Genevieve P.

Hiya! 🙂

Hope you don't mind if I jump in here, Paul.

@GDHernandez if you have a value in that checkbox column, such as a comma, then we can search for that value using a formula. For example:

=IF(Ordered@row = ",", ""

This will return a blank cell if it finds "," a comma in the checkbox column. Try adding that statement to the front of your current formula!

=IF(Ordered@row = ",", "", COUNTIFS(Ordered:Delivered, @cell = 1) / COUNTIFS(Ordered:Delivered, OR(@cell = 1, @cell <> 1)))

Cheers,

Genevieve