Stephen_IV
Well-known Member
- Joined
- Mar 17, 2003
- Messages
- 1,176
- Office Version
- 365
- 2019
- Platform
- Windows
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>
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>
