Percent complete when using date & checkbox field types

Natalia Kataoka

I have a task sheet that lists 7 items per row that need to be completed. I would like to calculate the percent complete of these tasks, but am running into trouble with my formula.

I have 4 checkbox columns, and 3 date columns. I may not use the check or a date in each, some will have "NA" or text, depending on the situation, not sure if this is what is messing with my formula?

Basically, I want to calculate the percentage based on which cells are not blank. The date fields seem to work fine, but the check boxes appear to read as 'not blank' whether they are checked or not. See screen shot.

This is the formula I have attempted to use:

=(7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, ""))) / 7

Paul Newcome

Does this work?

=(7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, OR(@cell = "", @cell = 0)))) / 7

Devin Lee

Try this

=1 - ((7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, =1) + COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, ISDATE(@cell)))) / 7)

Natalia Kataoka

Hi @Devin Lee , that formula almost works, though I am still running into issues if there is text in a cell vs a date/check that matches the field type.

For example, all rows below the green line should be 100%, except for the 2nd and 7th rows.

Paul Newcome

Does this work?

=(7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, OR(@cell = "", @cell = 0)))) / 7

Natalia Kataoka

That worked, thanks!!

Paul Newcome

Happy to help. 👍️