I'm looking to set up a formula that will show % completed based on checkboxes

Options

I'm looking to set up a formula that will show % based on checked/unchecked boxes. For example, in 1 column, I have 1 checked box, and 1 unchecked box. I am looking for it to say 50% are checked. The difficulty lies in the fact that it's information being pulled from another sheet, and it needs to be set within a certain date range

Best Answer

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓
    Options

    The function you're showing takes the average of the count based on a criteria. You still aren't dividing it by the total amount to get a percentage.

    Sorry, but if you count a total of 1 based on the criteria involved (checked cells) and take the average, you're calculating 1 over a total of 1 (because there's only 1 value checked) which equals 1.

    What are you using to calculate the total amount?

Answers

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Options

    Hello @dhuff11

    You could try using the COUNTIFS() function to enter multiple criteria such as whether its checked and within a certain time frame.

    Then use the COUNT() function to get a count on the entire range of values.

    Then divide the result by the total and get the percentage.

  • dhuff11
    dhuff11 ✭✭
    Options

    The issue I face with that formula is the scale of what it's going to be used for. This formula will be used across multiple cells and will be adjusted to each cell. I would end up with a massive amount of columns for each main column I'm trying to get a percentage from. Is there a way for it to be done all in 1 formula? The formula I have currently is:

    =AVG(COUNTIFS({Work Center}, "Department", {HRxHR}, 1, {Date}, AND(@cell > DATE(2023, 7, 15), @cell < DATE(2023, 7, 23)), {Shift}, "First"))


    HRxHR is the column with the checkboxes I'm trying to get the percentage of. This formula was able to return the correct quantity of checked boxes, but not the percentage compared to unchecked. Instead of showing 1, it should show 50%.

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓
    Options

    The function you're showing takes the average of the count based on a criteria. You still aren't dividing it by the total amount to get a percentage.

    Sorry, but if you count a total of 1 based on the criteria involved (checked cells) and take the average, you're calculating 1 over a total of 1 (because there's only 1 value checked) which equals 1.

    What are you using to calculate the total amount?

  • Rachel AR
    Options

    I actually needed this for a sheet.

    Instead of checkboxes we have the symbol (counting "blue"). But this would work for checkboxes also.

    I added a column to sum how many were checked over a series of columns.

    =COUNTIFS([BG PDF / Ver]@row:[AHA Status / Ver]@row, "blue")

    Then used that sum to calculate the % complete.

    Hope this helps.

  • dhuff11
    dhuff11 ✭✭
    Options

    Thank you all, this has been solved. I ended up having to create some helper columns and it helped tremendously.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!