How to use COUNTIFS with HAS and match 2 Columns while counting multiple selections in dropdown

How to use COUNTIFS with HAS and match 2 Columns while counting multiple selections in multi select dropdown ?

Solving for:

How many "Devon's Corner" Leads do we have by Location Type - "Bar" or "Brewery".. If any or both of these options are selected, they should be counted as 1

=COUNTIFS({Brand Type 1}, "Devon's Corner", {record type 3}, "Lead", {Location Type 2}, HAS(@cell, "Bar"), {Location Type 2}, HAS(@cell, "Brewery"))

It appears "Bar" is being counted, but not "Brewery" when selected. trying to get it to count if either or both are selected

Find more posts tagged with

Sales

Formulas

Reports

Accepted answers

Mark Cronk

Happy to help. Check your column properties. The column your COUNTIFS formula is in needs to be text/number. The Range {Location Type 2} needs to be a multi-select, {Brand Type 1} and {record type 3} can be either text/number or single select.

If the column properties are correct, does part of the formula work? Try just: =COUNTIFS({Brand Type 1}, "Devon's Corner", {record type 3}, "Lead")

Mark

All comments

Mark Cronk

Hi @Jade Alvarez ,

Try this:

=COUNTIFS({Brand Type 1}, "Devon's Corner", {record type 3}, "Lead", {Location Type 2}, OR(HAS(@cell, "Bar"), HAS(@cell, "Brewery")))

The OR function will count it if either are true.

Mark

Jade Alvarez

Hello @Mark Cronk - Thank you for the hand on this one

Came back as #Invalid Data Type

Mark Cronk

If the column properties are correct, does part of the formula work? Try just: =COUNTIFS({Brand Type 1}, "Devon's Corner", {record type 3}, "Lead")

Mark

Jade Alvarez

@Mark Cronk I think we NAILED it! You are a gentleman and a scholar. I really appreciate this one!

Happy holidays to you good sir!

Mark Cronk

Glad to be able to help. Thank you for using the Community. You're welcome to tag me on future posts if you think I can assist.

Happy Holidays!

Be Well,

Mark

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.