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
-
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
-
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
-
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!
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!