How to use COUNTIFS + CONTAINS in one formula?
Hello everyone,
Already grateful for your assistance in advance. I'm having some trouble coming up with a formula that can determine how often a multiple-choice topic appears in a column. I've used the COUNTIFS function and it worked well on the column with single-choice topics. But when I use the formula CONTAINS, it reads as invalid.
For the single topic it worked well and I've used:
=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 1}, "e-Learning Course")
I am trying to make the formula below work, but not sure if that makes sense:
=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 1}, "e-Learning Course", CONTAINS({BlueSky Courses for IE Competencies Range 3}, "Business Acumen"))
Explanation: How many topics of "Business Acumen" do I have IF it's e-learning (column product type below) and if the course is published (course status below)?
Status = BlueSky Courses for IE Competencies Range 4
Product Type = BlueSky Courses for IE Competencies Range 3
Thank you very much for your help!
Best Answer
-
You are getting close, but your COUNTIFS range criteria for your Competency is missing, and since you will be searching throughout an entire range with your CONTAINS, you want to use an @cell reference as CONTAINS doesn't naturally include a range. My below example returns 3.
So if I'm using your named ranges correctly, you can use:
=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 1}, "e-Learning Course", {BlueSky Courses for IE Competencies Range 3}, CONTAINS("Business Acumen", @cell))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Answers
-
You are getting close, but your COUNTIFS range criteria for your Competency is missing, and since you will be searching throughout an entire range with your CONTAINS, you want to use an @cell reference as CONTAINS doesn't naturally include a range. My below example returns 3.
So if I'm using your named ranges correctly, you can use:
=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 1}, "e-Learning Course", {BlueSky Courses for IE Competencies Range 3}, CONTAINS("Business Acumen", @cell))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
That worked!! Thank you so much Jason!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!