Using COUNTIFS based on conditions in another column

DMCARBRAY

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?

bisaacs

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!

Protonsponge

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

That should work for you….

DMCARBRAY

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?

Protonsponge

Does this work for you….

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

DMCARBRAY

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

Protonsponge

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

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

Protonsponge

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

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

Protonsponge

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

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!

Protonsponge

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.