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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.3K Get Help
- 446 Global Discussions
- 144 Industry Talk
- 477 Announcements
- 5K Ideas & Feature Requests
- 85 Brandfolder
- 151 Just for fun
- 72 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 302 Events
- 36 Webinars
- 7.3K Forum Archives