How to Calculate Percent Complete in Sheet Summary

KD_HUM

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, "<>"),"")

Genevieve P.

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

Genevieve P.

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

KD_HUM

Thank you so much! It works perfectly.

PChu02

Screen Cap.PNG

Hi @Genevieve P.

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

Thanks

Patrick Chu

Genevieve P.

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