#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Count Risks only when checkbox is open

Options
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭
Options

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

• edited 02/22/17
Options

Thank you! That worked perfectly.

• Options

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)))

• edited 02/22/17
Options

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.

• ✭✭✭✭✭✭
Options

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))

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

Hope this helps.

Craig

This discussion has been closed.