COUNTIFS not working correctly
I wan to count the number of projects that meet these criteria:
- Maintenance project, not an improvement project
- Not canceled (so status could be "Completed", "In Progress", "Not Started", etc.
- Matches a term code (e.g., 202110, 202090, 202060, etc.)
Here is the formula I am using:
=COUNTIFS([Project Type]:[Project Type], "Maintenance", [Term Submitted]:[Term Submitted], [Project Type]@row, [Revision Status]:[Revision Status], <>"Canceled")
- [Project Type] = column that is either "Maintenance" or "Improvement", submitted via a form
- [Term Submitted] = column that includes a formulate to determine the term based on user entry in a form, so it displays values such as 202110, 202090, 202060
- [Revision Status] = Status of project (e.g., "Canceled", "Completed", "In Progress", "Not Started"), selected manually from a drop-down box
Lower in the form, where I have a summary section, I have rows for each term (in the [Project Type] column, then the COUNTIFS formula in the column next to it.
Again, here is the formula:
=COUNTIFS([Project Type]:[Project Type], "Maintenance", [Revision Status]:[Revision Status], <>"Canceled", [Term Submitted]:[Term Submitted], [Project Type]@row)
In the screenshot above, the COUNTIFS looks at the [Project Type] column (is it a Maintenance project?), the [Revision Status] column (is the status anything by "Canceled"?), and the [Term Submitted] column (does it match the term code to the left (in [Project Type]@row). If so, count it.
However, i get 0 for all of the term codes. I've use a very similar formula for other things, just not with the [Term Submitted] column, so I am wondering if there is something special about it that Smartsheet is saying doesn't match the term code in [Project Type]@row. Is it a value that isn't matching? Is i trying to match a value to a string? Does it matter that [Term Submitted] value is the result of a formula?
I tried using the COUNTIFS without the [Term Submitted] criterion, and it works (see the Total row that just counts rows above using the [Project Type] and [Revision Status] columns. I am just not able to add this additional criterion to display a count by term code.