Countifs with similar strings

I am a bit stumpped - I am using the following formula to collect data from a multi select column

=COUNTIFS({Name}, [Name1]@row, {Unacceptable}, CONTAINS([Answer]@row , @cell ))

and for the most part it is fairly straigtforward to count the number of elements in the column but the formula does not work when the string has "overlap". I collect the string Q1 through Q56 in a multiselect and I want to (for example ) count all instances of Q2, the issue is that in a cell or the whole column may be entries like Q2, Q22, Q28 etc. which does not get along well with countifs and the has or contains functions. Any ideas on how to count Q2 seperately from Q20 - Q29 (same for Q1-Q5 and the associated number series)?

Find more posts tagged with

Accepted answers

All comments

jjg279

I think for a multi-select you have to use "HAS" instead of contains. Does this work:

=COUNTIFS({Name}, [Name1]@row , {Unacceptable}, HAS(@cell , [Answer]@row ))

KevinMio8

I had previously tried both HAS and CONTAINS but just discovered that "Q1" and "Q11" will each be recognized by countif when looking for "Q1" but "Q1 " (space after the 1 and before quotation mark) differentiates the search and the counts now work. Thanks.

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.