Using COUNTIFS based on conditions in another column

Options

I am using a COUNTIFS formula give me the number of times a project is given a priority rating higher than 2 Sample: =COUNTIFS([Production Priority]:[Production Priority], >2) and I have another column for Completed. How can I have the formula calculate projects with a priority rating higher than 2 that are not completed?

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @DMCARBRAY,

    You can take the COUNTIFS formula you have, and just add the "Not completed" to it:

    =COUNTIFS([Production Priority]:[Production Priority], >2, Completed:Completed, "Not Completed")

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 05/20/24
    Options

    =COUNTIFS([Production Priority]:[Production Priority], >2, Status:Status, <>"Not Complete")

    That should work for you….

  • DMCARBRAY
    Options

    Thanks for your answer… I really appreciate your help.

    My "Completed" column has these drop-down selections:

    Production Issue Resolved

    Avid Issue Resolved

    Transmission Issue Resolved

    Facility Issue Resolved

    I'd like this formula to let me know that the Production Issue Resolved option has not been selected. Can you solve for that scenario?

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 05/20/24
    Options

    Does this work for you….

    =COUNTIFS([Production Priority]:[Production Priority], >2, Status:Status, <>"Production Issue Resolved")

  • DMCARBRAY
    Options

    That is giving me a zero, but none of the Production Issues have been resolved. My column for completion is called "Completed". I used =COUNTIFS([Production Priority]:[Production Priority], >2, Completed:Completed, <>"Production Issue Resolved")

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 05/20/24
    Options

    I am not sure why that isn't working for you, can you send a screenshot of your sheet?

    I am getting 2 as a result in my sheet, is that what you are looking for?

  • DMCARBRAY
    Options

    Here are two screenshots. Thank you for taking a look!

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 05/21/24
    Options

    ah… I was thinking you wanted to sum the number of projects with a priority greater than 3 but after seeing the screen shots I’m thinking you want something different.


    Do you want to sum the various priorities >2 if the completed column does not indicate the associated priority as complete for each row?

  • DMCARBRAY
    Options

    Yes, that is exactly what I'm trying to get.

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    I think you want this as a formula… just check if I have all the column names and drop down names detailed correctly.

    =IF(AND([Transmission Priority]@row > 2, NOT(CONTAINS("Transmission Issue Resolved", Completed@row))), [Transmission Priority]@row, 0) + IF(AND([Avid Priority]@row > 2, NOT(CONTAINS("Avid Issue Resolved", Completed@row))), [Avid Priority]@row, 0) + IF(AND([Enterprise Priority]@row > 2, NOT(CONTAINS("Enterprise Network Issue Resolved", Completed@row))), [Enterprise Priority]@row, 0) + IF(AND([Facility Priority]@row > 2, NOT(CONTAINS("Facility Issue Resolved", Completed@row))), [Facility Priority]@row, 0)

  • DMCARBRAY
    Options

    Hi,

    Thank you for looking into this further. Unfortunately, this one returns a Circular Reference. I have simplified my set up as I think this is getting too complicated for me. I added an Automation to move completed projects to an Archive sheet so now I can show "current assigned projects" for each person, and all completed projects are moved to the Archive.

    Thank you again for your help on this… much appreciated!

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    Best of luck with your solution, I am confident you’ll get something that works for you! Although automation is a great tool and so many possibilities too!

    My apologies that the formula didn’t work for you in this case, I hope I didn’t over complicate things for you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!