How to Calculate Percent Complete in Sheet Summary

KD_HUM
KD_HUM
edited 02/13/23 in Smartsheet Basics

My sheet is used as a task checklist for projects. It currently has a 'Status' column for each Child task with 4 dropdowns (Not Started, In Process, Complete, N/A).

I am trying to create a percentage in Sheet Summary that calculates the number of fields that are marked 'Complete' in the selected cells.

Pulled this formula off another post, but can't seem to get it to work;

=iferror(countif(Column2:Column29, CONTAINS("COMPLETE"))/ countif(Column2:Column29, not(CONTAINS("N/A"), Column2:Column29, "<>"),"")

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @KD_HUM

    You're close! Try this:

    =IFERROR(COUNTIF(Column:Column, "COMPLETE") / COUNTIFS(Column:Column, <> "N/A", Column:Column, <> ""), "")

    This counts all the cells that have exactly the word "COMPLETE" in them, and the second formula counts how many of the cells are not (<>) "N/A" and also not blank. Is that what you were looking to do?

    Note that I removed the row references, so it's looking at your whole column. This will include new rows as they are added. If you want to exclude the first row, I would simply subtract 1 from the second formula so that you don't have to specify row numbers:

    =IFERROR(COUNTIF(Column:Column, "COMPLETE") / (COUNTIFS(Column:Column, <> "N/A", Column:Column, <> "") - 1), "")

    If this hasn't worked for you, it would be helpful to see a screen capture of your sheet identifying what your criteria is for counting your rows, but please block out sensitive data.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @KD_HUM

    You're close! Try this:

    =IFERROR(COUNTIF(Column:Column, "COMPLETE") / COUNTIFS(Column:Column, <> "N/A", Column:Column, <> ""), "")

    This counts all the cells that have exactly the word "COMPLETE" in them, and the second formula counts how many of the cells are not (<>) "N/A" and also not blank. Is that what you were looking to do?

    Note that I removed the row references, so it's looking at your whole column. This will include new rows as they are added. If you want to exclude the first row, I would simply subtract 1 from the second formula so that you don't have to specify row numbers:

    =IFERROR(COUNTIF(Column:Column, "COMPLETE") / (COUNTIFS(Column:Column, <> "N/A", Column:Column, <> "") - 1), "")

    If this hasn't worked for you, it would be helpful to see a screen capture of your sheet identifying what your criteria is for counting your rows, but please block out sensitive data.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Thank you so much! It works perfectly.

  • PChu02
    PChu02 ✭✭

    Hi @Genevieve P.

    I'm having a similar problem and was wondering if you can assist.

    Thanks

    Patrick Chu

  • Hey @PChu02

    Firstly, I would suggest putting this formula in a different column than the one you want to reference.


    Secondly, when you reference an entire column without specifying row numbers, you need to add the same column name twice, like so:

    Column:Column

    In your formula, I see you have Column:Status

    Instead, if you're referencing the Status column, use the reference:

    Status:Status

    See - Create a cell or column reference in a formula


    Let me know if updating the references has helped!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now