Completed project in a month

nroberson31 · 04/15/22

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

thanks

Jeff Reisman · 04/15/22

@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 · 04/15/22

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

Paul Newcome · 04/15/22

@Jeff Reisman I personally still use the AND function in COUNTIFS (and SUMIFS) to group my criteria together when I am referencing the same range multiple times. It makes it easier for me to read and make sure that I haven't forgotten anything in the super complex ones.

It also helps me find certain issues such as...

It looks like the above is referencing two separate sheets. Project Plan-700 and 3C-Database 700.

@nroberson31 Are you able to provide more details concerning your source data? Where is it coming from?

nroberson31 · 04/15/22

Thank you! it worked!!!!!

Jeff Reisman · 04/15/22

https://community.smartsheet.com/discussion/comment/326327#Comment_326327

I totally get that. I do use AND in there sometimes, especially on very long and complex formulas, in order to consolidate criteria and keep things organized. For less experienced users, trying to use it on relatively simple formulas adds a layer of complexity (and more parentheses!) that can cause syntax errors, making other formula issues more difficult to resolve.

Jeff Reisman · 04/15/22

https://community.smartsheet.com/discussion/comment/326332#Comment_326332

Happy to help!

Paul Newcome · 04/15/22

@Jeff Reisman I agree. Those extra parenthesis can really throw some things off if you aren't careful. Haha. You definitely make a valid point.

I'm also still trying to figure out those ranges. I know I am making an assumption, but it is something I just can't help but notice. Having "Range #" on the end lends itself towards not renaming ranges. If that's the case then the first portion should be the sheet name. Two sheets referenced within the same function like that should be throwing some kind of error I would think.

Jeff Reisman · 04/15/22

https://community.smartsheet.com/discussion/comment/326342#Comment_326342

I suspected that might be an issue too, but if the submitter says it works, I'm not going to argue it! 😀

Paul Newcome · 04/15/22

@Jeff Reisman I won't argue it either. I'm more curious if I may have missed some kind of update. Hahaha

Completed project in a month

Best Answer

Answers

Help Article Resources

Categories