CONTAINS to count cells with specific string of numbers

Karissa Herchenroeder

Is there a way to use COUNTIFS and CONTAINS to count cells that include a specific string of numbers as well as cells that include that string plus something else?

In the example below, I'm trying to count the number of students who are taking the course with the Code "20659," which should include those taking only that course (Student D and E) and those taking that course plus something else (Student C and F). Thus, the 4 students highlighted in purple. I tried the following formula, but it yields only 2:

=COUNTIFS(Code1:Code8, CONTAINS("20659", @cell))

If I could simply search for all students taking "LEAD 731," a comparable formula works like a charm: =COUNTIFS(Course1:Course8, CONTAINS("LEAD 731", @cell)) . However, I need a way to count students in LEAD 731 with Code "20659" separately from those in LEAD 731 with Code "21111."

Any brilliant ideas?

Paul Newcome

Try inserting a helper column and use

=Code@row + ""

(plus quote quote)

Then reference this helper column in your COUNTIFS.

Paul Newcome

Try inserting a helper column and use

=Code@row + ""

(plus quote quote)

Then reference this helper column in your COUNTIFS.

Karissa Herchenroeder

I do believe this will work. Thank you, Paul!

Paul Newcome

Happy to help. 👍️