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.
Here's one approach you may adapt accordingly:
=arrayformula(countif(","&'data sheet'!B:B&",","*,"&B2:B22&",*"))