# How to have Percent Complete for two columns?

Options
✭✭✭✭

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!

Tags:
«1

• ✭✭✭✭✭✭
Options

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:

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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%?

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭
Options

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

Options

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

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!