CONTAINS to count cells with specific string of numbers

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?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!