# Count anything that is passed due excluding certain criteria

edited 12/09/19

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:

• Employee

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

• ✭✭✭✭✭✭

This appears to be a duplicate of this post:

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!