I am having troubles integrating a contains formula into my Countifs formula.

I am wanting to count anytime a certain user has a certain type of follow up needed comment. The excel sheet comes from a workspace where users can select multiple options so I am looking to grab comments that contain a certain phrase. Below are my current formula, a screen shot of the metric sheet, and a filtered down version of the source sheet with the two main range columns highlighted.

=COUNTIFS({Retro Audit Report 2 Cred Required Comment}, (CONTAINS("Education", {Retro Audit Report 2 Cred Required Comment})), {Retro Audit Report 2 Default User}, User@row)

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Spencor_Luze

In your CONTAINS function, instead of re-typing the entire {reference} again, you'll want to use @cell to let the function know you want to search each cell in the range for your word.

See: Create Efficient Formulas with @cell and @row

Try this formula:

=COUNTIFS({Retro Audit Report 2 Cred Required Comment}, CONTAINS("Education", @cell), {Retro Audit Report 2 Default User}, User@row)

Note that I also removed the extra (parentheses) around the CONTAINS Function, you can list it right away. Let me know if this works for you and makes sense!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Spencor_Luze

In your CONTAINS function, instead of re-typing the entire {reference} again, you'll want to use @cell to let the function know you want to search each cell in the range for your word.

See: Create Efficient Formulas with @cell and @row

Try this formula:

=COUNTIFS({Retro Audit Report 2 Cred Required Comment}, CONTAINS("Education", @cell), {Retro Audit Report 2 Default User}, User@row)

Note that I also removed the extra (parentheses) around the CONTAINS Function, you can list it right away. Let me know if this works for you and makes sense!

Cheers,

Genevieve

Spencor_Luze

Aha! That did it. It makes sense to me now with how you laid it out. Thanks!

Genevieve P.

No problem! I'm glad this works for you 🙂

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.