How to use COUNTIFS + CONTAINS in one formula?

Michael Pereira

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!

Jason Tarpinian

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

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))

Michael Pereira

That worked!! Thank you so much Jason!!