Need Formula to capture all instances of text alone or with other text in a column

Currently using this formula to count all instances of a matching status with EBIT division. Sometimes EBIT is not the only division listed in the column. So a cell might have EBIT TBI. This formula works only if EBIT is alone.

=COUNTIFS({Status Phase}, [Primary Column]@row, {Sponsoring Division}, "EBIT")

I tried adding both CONTAINS and HAS in front of EBIT but both return invalid operations.

can anyone help?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Is the division column a multi-select dropdown?

Delilah

Yes but I have hidden column that copies it as text. Do I need to use the text column? And do I have to unhide it? Or is there a formula to use the original column.

Paul Newcome

You would use the HAS function with the original column.

=COUNTIFS(……………………, HAS(@cell, "EBIT"))

Delilah

That worked. I needed to add the @cell. Thanks very much!

Paul Newcome

Happy to help. 👍️

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.