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

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/24/20 Answer ✓

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

    Came back as #Invalid Data Type

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/24/20 Answer ✓

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • @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
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.