# 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?

• ✭✭✭✭✭
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!

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

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

That should work for you….

• Options

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?

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

Does this work for you….

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

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

• ✭✭✭✭✭
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?

• Options

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

• ✭✭✭✭✭
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?

• Options

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

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

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

• ✭✭✭✭✭
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!