Count Risks only when checkbox is open

I'm trying to create a formula that only counts the risks (High, Medium, Low) when the completed checkbox is not checked off. I have a formula for counting the number of risks (=COUNTIF(Risk:Risk, "High") + COUNTIF(Risk:Risk, "Medium") + COUNTIF(Risk:Risk, "Low")) but I have another column for "completed" that I want to include in this formula so I'm not counting risks when the risk has passed. I've attached an image to help understand what I'm asking for.

Find more posts tagged with

Formulas

IT & Operations

Comments

J. Craig Williams

Patricia,

You will want to use the COUNTIFS function.

=COUNTIFS(Risk:Risk, "High", Completed:Completed, 1)

(repeated for the other two risks)

Hope this helps.

Craig

Patricia Thurston

Thank you! That worked perfectly.

Patricia Thurston

Hi, I have another question about the formula for this. I only want to count the late tasks if the Risk column is empty. How would I include that in the statement under Late Tasks? My formula for late tasks is: =IF(Completed2 <> 1, IF(TODAY() - [Due Date]2 > 0, 1, IF(TODAY() - [Due Date]2 > -3, 1, 0)))

Patricia Thurston

Here is the formula I'm trying to create under the "Late Tasks" column: =IF(Risk3 = "Empty"), IF(Completed3 <> 1, IF(TODAY() - [Due Date]3 > 0, 1, IF(TODAY() - [Due Date]3 > -3, 1, 0))))

If the Risk column is empty, then count it as a late task, otherwise, ignore it.

J. Craig Williams

Patricia,

I don't understand your current formula.

TODAY() - [Due Date]2

will be positive for items in the past and negative for items in the future.

Your next IF's result in a "1" for anything three days out and in the past.

You should get the same results with:

=IF(Completed2 <> 1, IF(TODAY() - [Due Date]2 > -3, 1, 0))

To answer you question, assuming I'm right about your formula, this might work:

=IF(AND(Completed2 <> 1, ISBLANK(Risk2)), IF(TODAY() - [Due Date]2 > -3, 1, 0))

Hope this helps.

Craig