COUNTIFS & CONTAINS

Katie Gallagher

I'm having trouble creating a formula to get the results that I need using advanced functions.

I created a COUNTIFS formula by creating two COUNTIF formulas, then combining them:

COUNTIF A:

=COUNTIF({Buyer's Journey/ Sales Stage}, "SALES ENABLEMENT")

COUNTIF B:

=COUNTIF({Product}, "Teaching Strategies [Solution Level]")

COUNTIFS:

=COUNTIFS({Buyer's Journey/ Sales Stage}, "SALES ENABLEMENT", {Product}, "Teaching Strategies [Solution Level]")

I'm encountering a problem with both the COUNTIF B formal AND THE COUNTIFS formula.

My "Product" column in the referenced sheet is a multi-select dropdown.

Both formulas are ONLY counting items in the result if ONLY "Teaching Strategies [Solution Level]" is select as the product.

I want it to count all items that include "Teaching Strategies [Solution Level]", even those for which other products are selected in that column as well.

I"m wondering if I need to use the CONTAINS or HAS functions, but I really don't know how to do that. @Genevieve P can you help me?

Find more posts tagged with

Accepted answers

Genevieve P.

Hi Katie,

You are absolutely correct, you'll want to use the HAS function so that it recognizes your value even when it's included with other values.

Try this:

=COUNTIFS({Buyer's Journey/ Sales Stage}, "SALES ENABLEMENT", {Product}, HAS(@cell, "Teaching Strategies [Solution Level]"))

So after the column reference, I used HAS to then specify the range (@cell, or to look in each cell of the previously stated column), and then the criteria (your value).

This will count rows that have both "SALES ENABLEMENT" in the Sales Stage column AND that have "Teaching Strategies [Solution Level]" as one of the selected options, even when it's with others.

Let me know if this works for you!

Cheers,

Genevieve

All comments

Genevieve P.

Hi Katie,

You are absolutely correct, you'll want to use the HAS function so that it recognizes your value even when it's included with other values.

Try this:

=COUNTIFS({Buyer's Journey/ Sales Stage}, "SALES ENABLEMENT", {Product}, HAS(@cell, "Teaching Strategies [Solution Level]"))

So after the column reference, I used HAS to then specify the range (@cell, or to look in each cell of the previously stated column), and then the criteria (your value).

This will count rows that have both "SALES ENABLEMENT" in the Sales Stage column AND that have "Teaching Strategies [Solution Level]" as one of the selected options, even when it's with others.

Let me know if this works for you!

Cheers,

Genevieve

Katie Gallagher

Thank you so much Genevieve! This is awesome!

Genevieve P.

No problem! 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.