COUNTIFS not working correctly

Art Schneiderheinze

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.

Any thoughts?

Mark Cronk

Hi @aschneiderheinze74206 ,

My only other idea would be to add a column [term value] with the column formula =VALUE([term submitted]) and run your countif against that column instead of [term submitted].

Mark

Mark Cronk

Hi @aschneiderheinze74206 ,

Try

=COUNTIFS([Project Type]:[Project Type], "Maintenance", [Revision Status]:[Revision Status], <>"Canceled", [Term Submitted]:[Term Submitted], VALUE([Project Type]@row))

Your project type looks like a dropdown. If so, smartsheets wants to treat is as text instead of a number. Adding the VALUE function would be the fix.

Work?

Mark

Art Schneiderheinze

I tried this:

=COUNTIFS([Project Type]:[Project Type], "Maintenance", [Term Submitted]:[Term Submitted], VALUE([Project Type]@row), [Revision Status]:[Revision Status], <>"Canceled")

It still shows 0.

The [Project Type] and [Revision Status] columns are drop-down. However, [Term Submitted] is a formula, which displays a term code (e.g., 202110) based on the date the form was submitted. It is a Text/Number column.

Looking at the rows manually, Smartsheet should display "5" for the first term code (202110), since there were five "Maintenance" requests that are "Pending" or "Not Started" or "Completed" (that is, not "Canceled"). Yet, it shows 0.

If I only use the "Maintenance" and "<>Canceled" criteria, it displays "185" but that counts all "Maintenance" requests with statuses that were not "Canceled". I just need it to also consider the Term Submitted.

Mark Cronk

Hi @aschneiderheinze74206 ,

A couple things. First, because your summary rable resides in rows you are using in your calculation, you have to limit the formula ranges by entering row numbers. See below.

Second, confirm your drop down columns are set to single select. Confirm the options are correct - no spaces before or after the text.

=COUNTIFS([Project Type]1:[Project Type]18, "Maintenance", [Term Submitted]1:[Term Submitted]18, VALUE([Project Type]@row), [Revision Status]1:[Revision Status]18, <>"Canceled")

Consider moving your summary to its own sheet, or moving it out of the column you're using for count. That way you can use the full colum ranges and colum formulas.

Help?

Mark

Art Schneiderheinze

Thanks for the suggestion. I did try to move the term codes out of the column that is used in the criteria. I've done this with other formulas on the same sheet without a problem, but I tried it specifically for this COUNTIFS. I didn't work. Still seeing "0".

Yes, the drop-down columns are all single select.

It's just the weirdest thing! I tested that the numbers in the actual rows don't have spaces or any extra characters. I created a Test column, and tried ="*"+[Term Submitted]@row+"*" and get just the term code, such as 202110. I altered the COUNTIFS to look at the Test column instead, and I still get 0 results.

As I mentioned, if I exclude the [Term Submitted] criterion, I get the results (all rows with Maintenance and anything but "Canceled" in the Review Status. So, I know those two criteria work fine. It's just not allowing me to include the [Term Submitted]. or at least it's not finding any matches with rows that meet all three criteria. For 202110, for example, if I manually count the rows, I should see "5" as the result, since there are five rows with [Term Submitted] = 5, [Project Type] = "Maintenance", and [Revision Status] either "Completed" or "In Progress" (in other words, not "Canceled").

Mark Cronk

Hi @aschneiderheinze74206 ,

My only other idea would be to add a column [term value] with the column formula =VALUE([term submitted]) and run your countif against that column instead of [term submitted].

Mark

Art Schneiderheinze

Ah-ha! That worked! Thanks very much for your time and suggestions!

Mark Cronk

Wow, got lucky. Gald you found a solution. Thank you for contributing to the Community.

Mark

danielma

Hi!

I am not sure if you still need this, but maybe someone else will. I had the same issue and the reason why converted cells (from number to text) are not readable by the COUNTIF function is because they are not just simple "1234", they are actually " ' 1234". So in your search try

COUNTIFS([Project Type]1:[Project Type]18, ("'Maintenance")) or

COUNTIFS([Project Type]1:[Project Type]18, (" ' " + [Project Type]@row)) - for when you are looking for distinct values.