What formula do I use to count a specific text when multiple text values are in a cell?

I have a column with a drop down where you can select multiple options for one cell. It is tracking who is affected so it can list multiple departments. I want to track by department so I am trying to figure out how to count only a specific word in a cell when there are multiple valves in the cell. The formula I am using keeps giving me zero. What am I missing? Any help you can provide is greatly appreciated!! 😊

=COUNTIFS({ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1}, AND(CONTAINS("WOA", {ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1})))

Find more posts tagged with

Sharing and Collaboration

Formulas

Support

Solution Center

Countif

Accepted answers

Paul Newcome

You don't need the AND function, and the "range" in the CONTAINS should be "@cell". I also suggest using HAS when evaluating a multi-select type column.

=COUNTIFS({ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1}, HAS(@cell, "WOA"))

All comments

Paul Newcome

You don't need the AND function, and the "range" in the CONTAINS should be "@cell". I also suggest using HAS when evaluating a multi-select type column.

=COUNTIFS({ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1}, HAS(@cell, "WOA"))

Jessica_Sabin

@Paul Newcome Thank you. :) I am highlighting an entire column, not a cell which is why its listed like that. Is there something else I need to do for a column formula?

Paul Newcome

Right. The @cell tells the formula to run the HAS function on a cell by cell basis through the previously established range.

Jessica_Sabin

Thank you so much @Paul Newcome! That worked. You are a lifesaver. 😊

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