"CountIF" issues

D.wilson

Hi everyone. I am trying to get Smartsheet to run a basic COUNTIF function, and it's finding #NO MATCH. The formula is: =COUNTIF([PE Manager]:[PE Manager], CONTAINS("Anna Richey", @cell)), where I am trying to count all instances of the name "Anna Richey" showing up in the [PE Manager] column. Any ideas what I'm doing wrong?

I've also tried: =COUNTIF([PE Manager]:[PE Manager], HAS(@cell, "Anna Richey"))

AND I've tried: =COUNTIF([PE Manager]:[PE Manager], "Anna Richey")

I know there are several instances of "Anna Richey" in the column.

THANK YOU!

D.wilson

Thank you very much everyone! I was pulling the data in the PE Manager column in from another sheet, and for some reason, Smartsheet wouldn't count in that column. When we ran a "datamesh" instead of just a reference formula to pull into the sheet, it worked! :)

Thanks for all your help!

Dahvi

Colleen Patterson

@D.wilson

The formula =COUNTIF([PE Manager]:[PE Manager], "Anna Richey") would work if you have the column for PE Manager as either a contact or text, as long as the text is a match - i.e. no extra spaces.

If you are OUS, you would need to change the , to a ; in the formula.

This would work in the sheet itself or within a summary field within the source sheet. If you are referencing a different sheet, you would need to adjust your formula to account for that: =COUNTIF({PE Manager}, "Anna Richey"), where PE Manager is the column that is being counted.

Emilio Wright

@D.wilson Firstly, in all instances, I was able to find values using HAS. What is your column type for PE Manager?

I copied your formula straight from your post:

=COUNTIF([PE Manager]:[PE Manager], CONTAINS("Anna Richey", @cell))

=COUNTIF([PE Manager]:[PE Manager], HAS(@cell, "Anna Richey"))

I created a column that is a Text/Number type and entered the name Anna Richey. I tried using CONTAINS and HAS. The CONTAINS formula was able to find 4 instances in my example. The HAS formula was able to find 3 instances. This is expected because HAS looks for an exact match, whereas CONTAINS does not.

When I changed the column to a Contact Type - Single Select, HAS was able to find 4 instances of the Name Anna Richey and CONTAINS was able to find 0.

When I changed the column to a Contact Type - Multi Select, HAS was able to find 4 and CONTAINS was able to find 3.

I prefer using HAS when I know the value I am going to look for and should expect the exact value. I use CONTAINS very rarely. As you can tell, CONTAINS does not seem to work for Single Select Contact List columns.

D.wilson

Thank you very much everyone! I was pulling the data in the PE Manager column in from another sheet, and for some reason, Smartsheet wouldn't count in that column. When we ran a "datamesh" instead of just a reference formula to pull into the sheet, it worked! :)

Thanks for all your help!

Dahvi