Count anything that is passed due excluding certain criteria

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 

Tags:

Comments

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!