google-sheetsgoogle-sheets-formula

How to count exact text string matches when there is a partial text string overlap, such as counting both pills and pillaged pills but not pillaged


I am flummoxed but confident there is a way to do this. I just can’t see it.

I want to count the number of class options for each age, but I can't figure out a way to count ‘5 y/o’ without also counting ‘15 y/o’ — or a way to count all classes for ‘5 y/o’ including those that are also for ‘15 y/o’.

My downloaded data looks like this:

Class Age(s)
Class Title 1 0 y/o,1 y/o,2 y/o,3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 2 1 y/o,2 y/o,3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 3 2 y/o,3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 4 3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 5 4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 6 5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 7 6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 8 7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 9 8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 10 9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 11 10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 12 10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o
Class Title 13 10 y/o,11 y/o,12 y/o,13 y/o,14 y/o
Class Title 14 0 y/o,1 y/o,2 y/o,3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o
Class Title 15 3 y/o,4 y/o,5 y/o,6 y/o
Class Title 16 5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o
Class Title 17 0 y/o,1 y/o,2 y/o,3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 18 1 y/o,2 y/o,3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 19 2 y/o,3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o
Class Title 20 3 y/o,4 y/o,5 y/o,6 y/o,7 y/o,8 y/o,9 y/o,10 y/o,11 y/o,12 y/o,13 y/o,14 y/o,15 y/o,16 y/o,17 y/o,18 y/o,19 y/o

The results that I are want are:

Age Number of Class Options for Age
0 y/o 3
1 y/o 5
2 y/o 7
3 y/o 10
4 y/o 11
5 y/o 13
6 y/o 14
7 y/o 14
8 y/o 15
9 y/o 16
10 y/o 18
11 y/o 17
12 y/o 17
13 y/o 17
14 y/o 17
15 y/o 16
16 y/o 16
17 y/o 16
18 y/o 16
19 y/o 15
Adult 20+ 0

If it matters, the number of classes varies by semester.

The following counts both classes for ‘5 y/o’ and classes for ‘15 y/o’ that are not also for ‘5 y/o’

=COUNTIFS('data sheet'!$A$2:$A,"<>",'data sheet'!$B$2:$B,"*"&A2&"*")

The following fails to count classes that are for both ‘5 y/o’ and ‘15 y/o’.

=COUNTIFS('data sheet'!$A$2:$A,"<>",'data sheet'!$B$2:$B,"*"&A2&"*",'data sheet'!$B$2:$B,"<>*"&A12&"*")

Thank you.

link to editable Google Sheet


Solution

  • Here's one approach you may adapt accordingly:

    =arrayformula(countif(","&'data sheet'!B:B&",","*,"&B2:B22&",*"))
    

    enter image description here