Completed project in a month

how to count completed projects within a data range. This is the formula I have: =COUNTIFS({Project Plan-700 Range 2}, "Complete", AND({3C-Database 700 Range 2} >= DATE(2022, 4, 1), {3C-Database 700 Range 2} <= DATE(2022, 4, 30)))


Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @nroberson31 What kind of error are you getting? I'm guessing it's an #INVALID DATA TYPE? or maybe #UNPARSEABLE.

    We want to have a formula where COUNTIFS is not trying to evaluate any blank date fields (which can cause that error.) COUNTIFS evaluates criteria from left to right, so we'll exclude any blank date rows first. The COUNTIFS syntax is COUNTIFS(range, criteria, range2, criteria 2...) so make sure your range and criteria are always separated by a comma. Also, AND is really not needed in a COUNTIFS since by its nature, COUNTIFS counts rows were all the conditions are true. Essentially, AND is built-in. Try this:

    =COUNTIFS({Project Plan-700 Range 2}, "Complete", {3C-Database 700 Range 2}, ISDATE(@cell), {3C-Database 700 Range 2}, @cell >= DATE(2022, 4, 1), {3C-Database 700 Range 2}, @cell <= DATE(2022, 4, 30))


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!