CountIF, AND, OR, & NOT Statements -- Which Order?

Alex Beaupre

Hi! Was hoping someone might be able to provide some insight here. I am trying to work out a status formula where a percentage is calculated based on row values that have text in them. This would also be dependent on different status within the same row. Here's what I've worked out so far.

=COUNTIF([GMB URL]@row:[GMB Status]@row, OR(@cell = true, ISTEXT(@cell), AND([GMB Status]@row = "Published", NOT([GMB Status]@row = "Not Started"), OR([GMB Status]@row = "Canceled")))) / 3

The formula works, but the problem is that I do not want to calculate the percentage when a project is "Not Started", and I'm not sure where to but the following part of the formula without it still calculating the percentage (33% for all not started projects)

NOT([GMB Status]@row = "Not Started")

Any ideas!?

Ramzi K

@Alex Beaupre

Your check for Not Started should be first. Like this:

=IF([GMB Status]@row = "Not Started", 0, COUNTIF([GMB URL]@row:[GMB Status]@row, OR(@cell = true, ISTEXT(@cell), AND([GMB Status]@row = "Published", NOT([GMB Status]@row = "Not Started"), OR([GMB Status]@row = "Canceled")))) / 3)

I hope this helps.

Cheers,

Ramzi

Ramzi K

@Alex Beaupre

Your check for Not Started should be first. Like this:

=IF([GMB Status]@row = "Not Started", 0, COUNTIF([GMB URL]@row:[GMB Status]@row, OR(@cell = true, ISTEXT(@cell), AND([GMB Status]@row = "Published", NOT([GMB Status]@row = "Not Started"), OR([GMB Status]@row = "Canceled")))) / 3)

I hope this helps.

Cheers,

Ramzi