Formula only for columns that meet criteria

Platform discussions Special workflows & use cases
,
1

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:

  1. There are 5 status columns to acommodate the months with 5 weeks
  2. The fomula should only get the % of the columns that are either “complete” and “not complete”
  3. 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)

Thank you!

2

Hi Ana,

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%.

image
image1700×344 26.3 KB

It is also worth noting you can edit which status is counted as ‘complete’ in the settings for each column.

If this doesn’t help please do reach out and we can work on looking at a formula to solve this.

Kind Regards,
Jason

3

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.

Thank you!

1 Like
4

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?

5

@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.

6

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.

(SUM(IF({Status}="Done",1,0),IF({Status 2}="Done",1,0),IF({Status 3}="Done",1,0))/SUM(IF(NOT(OR({Status}="OOO",{Status}="N/A")),1,0),IF(NOT(OR({Status 2}="OOO",{Status 2}="N/A")),1,0),IF(NOT(OR({Status 3}="OOO",{Status 3}="N/A")),1,0)))*100
7

@codyfrisch this works great!! Thank you so much, I already completed the formula and it’s doing everything I needed it to.

1 Like
8

You’re welcome! Glad it worked!

9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.