CountIFS contact is in a contact list

=COUNTIFS({TEST Range 1}, ="*Name*", {TEST Range 6}, ="DO")

Hi all, this is a helper sheet used to get the counts of different conditions. Test Range 1 is a contact list that can contain multiple contacts, how can i make it so that i can check if "Name" is in the list.

It is currently returning 0 when it should be finding 1 row.

Find more posts tagged with

Contacts

User List

Countif

Accepted answers

Paul Newcome

Try a HAS function.

=COUNTIFS({TEST Range 1}, HAS(@cell, "Name"), {TEST Range 6}, @cell = "DO")

All comments

Paul Newcome

Try a HAS function.

=COUNTIFS({TEST Range 1}, HAS(@cell, "Name"), {TEST Range 6}, @cell = "DO")

NoahB

@Paul Newcome Yep that did it thanks! I also tried using CONTAINS, but it would always return 0 as well. Do you know why contains doesn't work in this situation / when to use has vs contains?

Paul Newcome

CONTAINS just doesn't work on contact type columns. I believe it may have to do with the way the data is stored on the back-end vs how it is displayed, but that's just a guess on my part.

HAS is designed specifically for multi-select dropdowns and contact type columns. It is used to identify as specific selection as opposed to a certain string.

Lets say you have a multi-select dropdown column with these possible selections:

Let

Level

Slew

If you use CONTAINS for "le", It would count for all of those because they all contain "le". HAS on the other hand would only count rows where "Le" specifically was selected and ignore the others.

If you are familiar with filters whether that be in sheets, automations, or reports, think of CONTAINS as exactly that "Contains", but HAS would be more like "Is one of".

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.