Count Unique for children who have not taken a test

[Stephen_IV]

Good morning,
I have some data that has a number of tests on it that kids are allowed to take. The first problem is that kids can take any number of tests. The second problem is that the for each test that the kid is enrolled in, it will show a 1 for test taken and a 0 for test not taken. As long as a kid has a 1 then that means that a kid has taken a test. I need to count how many kids did not take a test. based on the school. This is the formula that I used if I know how many tests a child has taken =SUM(IF(FREQUENCY(IF(($B$2:$B$27=0)*($C$2:$C$27=$I2),$A$2:$A$27),$A$2:$A$26)=10,1)) but it obviously does not work because each child will take a different amount of tests.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>ID</th><th>Test Taken</th><th>School</th></tr></thead><tbody>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>1</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>9999</td><td>0</td><td>xxxx</td></tr>
<tr><td>1111</td><td>0</td><td>yyyy</td></tr>
<tr><td>1111</td><td>0</td><td>yyyy</td></tr>
<tr><td>1111</td><td>0</td><td>yyyy</td></tr>
<tr><td>1111</td><td>0</td><td>yyyy</td></tr>
<tr><td>1111</td><td>0</td><td>yyyy</td></tr>
<tr><td>1111</td><td>0</td><td>yyyy</td></tr>
<tr><td>2222</td><td>0</td><td>yyyy</td></tr>
<tr><td>2222</td><td>1</td><td>yyyy</td></tr>
<tr><td>2222</td><td>0</td><td>yyyy</td></tr>
<tr><td>2222</td><td>0</td><td>yyyy</td></tr>
<tr><td>2222</td><td>1</td><td>yyyy</td></tr>
<tr><td>2222</td><td>1</td><td>yyyy</td></tr>
<tr><td>2222</td><td>0</td><td>yyyy</td></tr>
</tbody></table>

This is what I am looking for
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>ID</th><th>Test Taken</th></tr></thead><tbody>
<tr><td>xxxx</td><td>0</td></tr>
<tr><td>yyyy</td><td>1</td></tr>
<tr><td>zzzz</td><td>0</td></tr>
</tbody></table>

 

[Eric W]

Maybe:

	A	B	C	D	E	F
1	ID	Test Taken	School		School	# students who have taken no tests
2	9999	0	xxxx		xxxx	0
3	9999	0	xxxx		yyyy	1
4	9999	0	xxxx
5	9999	0	xxxx
6	9999	1	xxxx
7	9999	0	xxxx
8	9999	0	xxxx
9	9999	0	xxxx
10	9999	0	xxxx
11	9999	0	xxxx
12	1111	0	yyyy
13	1111	0	yyyy
14	1111	0	yyyy
15	1111	0	yyyy
16	1111	0	yyyy
17	1111	0	yyyy
18	2222	0	yyyy
19	2222	1	yyyy
20	2222	0	yyyy
21	2222	0	yyyy
22	2222	1	yyyy
23	2222	1	yyyy
24	2222	0	yyyy
25

<tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula E2 {=IFERROR(INDEX($C$2:$C$30,MATCH(0,COUNTIF($C$2:$C$30,"<"&$C$2:$C$30)-SUM(COUNTIF($C$2:$C$30,F$1:F1)),0)),"")} F2 {=IF(E2="","",SUM(SIGN(FREQUENCY(IF($C$2:$C$30=E2,IF(COUNTIFS($A$2:$A$30,$A$2:$A$30,$B$2:$B$30,1,$C$2:$C$30,E2)=0,$A$2:$A$30)),$A$2:$A$30))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you already have a list of schools, you can use that. Otherwise, the E2 formula creates a sorted unique list of the schools. The F2 formula counts how many students at each school have take no tests. This assumes the student ID is numeric, but it's easy enough to change if that's not true.

 

[Stephen_IV]

Eric W! Amazing, I have been on this for days trying to figure it out. Thanks to you I am good!!! Appreciate all of your help and guidance. Thank you again!!!!!!!!!

 

[Eric W]

You're welcome!