How to Count multiple values within a single cell using COUNTIF Statement

Hi Team,

Have been racking my brain on this one.

Looking to try and count the number of times a specific word is mentioned in a column, however I cannot see to capture the cells where there is multiple words added. .

I am referencing a Raw Data Sheet to a Calculation sheet, to bring back some general figures, but sadly not able to reference the multi word Cells.

Any help would be appreciated :)

Thanks,

Dan

Find more posts tagged with

Accepted answers

ProductDan

Thanks Mr Chris

All comments

=Chris Palmer

Hello @ProductDan,

I was able to replicate your scenario and the COUNTIFs formula worked when I removed "&" from the General Issue column.

=COUNTIF([Drop Down Column]:[Drop Down Column], [General Issue]@row)

ProductDan

@Mr. Chris ,

Thank you for responding, Sorry this hasn't quite gone to plan for me.

My Raw Data Sheet is separate to my Calculation Sheet so I am refencing this if it makes much of a difference?

My Calculation Document is trying to capture the number of times each Issue has been mentioned

What its not capturing is when a cell has more than one issue

So in theory if the formula was working correctly I would See Service and RFP numbers a little higher

The Formula I have tried to far are below, Note I am referencing the Raw data at the beginning:

=COUNTIFS({2024 Red List Clients General Issue}, "Pricing", "Service")

=COUNTIFS({2024 Red List Clients General Issue}, "Pricing" OR "Service")

=COUNTIF({2024 Red List Clients General Issue}, "Pricing" OR "Service, @cell")

=COUNTIF({2024 Red List Clients General Issue}, OR ("Pricing", @cell), CONTAINS("Service, @cell")))

All give the same response: #UNPARSEABLE

Thanks,

Product Dan

=Chris Palmer

Hi Dan,

If I'm understanding your needs correctly, it might be helpful to simply reference the cell in your calculations

in [General Issue]@row like so:

=COUNTIFS({2024 Red List Clients General Issue}, [General Issue]@row)

I was able to Count "Pricing Service" when found with a total of 3 times.

I was able to count all these instances on my Calculations Sheet referencing my Testing Sheet.

You may want to consider copying all data in the Drop Down Column, removing duplicates, then have the remainder be under the "General Issue" column in your calculation sheet. Then you will count every instance.

ProductDan

Thanks Mr Chris

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.