Count anything that is passed due excluding certain criteria

bmcmichael48661

It is not supposed to count anything that is closed, lesson learned, or duplicate. 

The current formula is:

=COUNTIFS(Due:Due, <TODAY(), Disposition:Disposition, <>"Complete") 

This formula is counting those things, however, I need to count anything that is passed due and not closed, lesson learned or duplicate. I am utilizing a drop down box, too. 

Thoughts?

 

Branden 

Shaine Greenwood

Hi Branden,

If you're wanting to count all of that in one function, then you can add criteria to your COUNTIFS to capture this with an OR statement:

=COUNTIFS(Due:Due, <TODAY(), OR(Disposition:Disposition, <>"Complete", Disposition:Disposition, "Open", Disposition:Disposition, "Lesson Learned",Disposition:Disposition, "Duplicate"))

Note a few things on this example formula:

• It's an example and may not reflect how your sheet is set up, please refrain from copying and pasting it (it may not work in your sheet).
• Change "open" to whichever status you use to indicate that something is still active in your dropdown.
• Note that I've removed the <> from the other criteria in the OR function, as we're counting whether Disposition is equal to those values.

Otherwise, you'll want to use separate COUNTIFS in different cells:

=COUNTIFS(Due:Due, <TODAY(), Disposition:Disposition, <>"Complete") 

=COUNTIFS(Due:Due, <TODAY(), Disposition:Disposition, "Open") 

=COUNTIFS(Due:Due, <TODAY(), Disposition:Disposition, "Lesson Learned")

=COUNTIFS(Due:Due, <TODAY(), Disposition:Disposition, "Duplicate")  

J. Craig Williams

This appears to be a duplicate of this post:

https://community.smartsheet.com/discussion/countifs-formula-help-0