"CountIF" issues

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!

Find more posts tagged with

Accepted answers

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

All comments

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

Thanks for all your help!

Dahvi

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.