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

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. 

DRAFT_of_Issues_and_Action_Items_-_Smartsheet_com.png

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

  • Patricia Thurston
    edited 02/22/17
    Options

    Thank you! That worked perfectly. 

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

     

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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))

     

    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

     

     

This discussion has been closed.