COUNTIFS & CONTAINS
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?
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much Genevieve! This is awesome!
-
No problem! Happy to help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!