Hi everyone! Wanted to see if it was possible to calculate a % of completeness based on multiple status columns but that only takes into account certain statuses each time??

In this scenario we have a report that has to be filled by employees every week. But we need the formula to take into account only the statuses of “complete” and “not complete”.

Other things to take into account are:

There are 5 status columns to acommodate the months with 5 weeks

The fomula should only get the % of the columns that are either “complete” and “not complete”

We also have “OOO” and “N/A” statuses for when the employee is permited not to send the report a certain week.

These are scenarios of what the formula would need to do:

On a 5 week month, where 3 statuses are “complete”, 1 is “N/A” and 1 status is “not complete” get the answer “75%”

On a 4 week month, if an employe has 2 “complete” and 2 “not complete” get the answer “50%”. (this would mean theres an additional 5th “N/A” status)

I am wondering if the “Progress Tracking” column type would be the solution here. With that, as soon as one of the 5 statuses is marked as ‘complete’, then it updates the progress tracking column. For example, if 1 status is marked as complete, and the other 4 are not, then the progress tracking will show 20%.

Hi @ioi_jason! I did think of using the progress column and I think I might have to. Even though the total will always be skewed since it will take into account all columns everytime (which is was what I wanted to work around), I think its just the simplest way to measure and will have to do for now since I dont have any other idea on how to solve it.

If I understand correctly, if a status is marked OOO or N/A then the formula should act as if the column doesn’t exist in terms of the calculation. So five columns, with one being OOO the percentage should calculate as if there were only four columns?

@codyfrisch that’s exaclty right. In my head the formula would kind of function as a sumif¿? adding only the columns that meet the criteria of “complete” and “not complete” whoever many they may be.

I’m thinking this is a good start, though I have only taken it to three columns. The part left is going to be the calculation of if its a 5 week or 4 week month. Which you will need to figure out , and make part of the conditionals for the last statuses in the below.