How to Calculate Percent Complete in Sheet Summary
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
-
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
-
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.
-
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives