Hmm, the theory behind it isn't that hard, but the details are a bear! It's very easy to get rows/columns mixed up in a formula like this. Also, understanding how Excel multiplies 2 arrays together can be a little headache-inducing. But I'll give it a shot.
=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",$A$1:$I$1)),IF(ISNUMBER(SEARCH(
","&$A$2:$I$5&",",","&D$8&",")),1)*($L$2:$L$5=$B10)))
The section in red creates an internal array containing all of the values from A2:I5 surrounded by commas:
| A | B | C | D | E | F | G | H | I |
|---|
| 14 | ,a, | ,b, | ,b-, | ,e, | ,d, | ,d, | ,i, | ,s, | ,s+, |
| 15 | ,b, | ,d, | ,b, | ,f, | ,s, | ,d+, | ,j, | ,m, | ,m-, |
| 16 | ,c, | ,s, | ,b+, | ,g, | ,m, | ,s-, | ,k, | ,b, | ,1, |
| 17 | ,d, | ,m, | ,d-, | ,h, | ,b, | ,s+, | ,l, | ,d, | ,b+, |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4
Internally, the array is represented by {}, with a comma indicating next item, and a semicolon indicating next row, but this is easier to visualize.
=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",$A$1:$I$1)),
IF(ISNUMBER(SEARCH(","&$A$2:$I$5&",",
","&D$8&",")),1)*($L$2:$L$5=$B10)))
The part in red now puts commas around the header, so it looks like: ",B-,S1,S2,S3,B-1,B-2,B-3,". Since this is an array formula, now it checks to see if each value is in the header. We had to put commas around the values to make sure we don't get unintentional matches. A SEARCH will return either the offset into the string, or an error. So if it's a number (ISNUMBER), we know that we have a match, and we put a 1 in that location in the array, otherwise it defaults to FALSE:
| A | B | C | D | E | F | G | H | I |
|---|
| 14 | FALSE | FALSE | 1 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 15 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 16 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 17 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4
This table now represents all the cells in A2:I5 that match one of the codes in the header.
=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",$A$1:$I$1)),IF(ISNUMBER(SEARCH(","&$A$2:$I$5&",",","&D$8&",")),1)*
($L$2:$L$5=$B10)))
This part checks for the gender. It creates a vertical array of TRUE/FALSE, based on whether L2:L5 equals the gender code in B10.
<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4
=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",$A$1:$I$1)),IF(ISNUMBER(SEARCH(","&$A$2:$I$5&",",","&D$8&",")),1)
*($L$2:$L$5=$B10)))
Now we multiply the 2 arrays together. In this type of array multiplication, all the values in the top row of the first array are multiplied by the top value of the second array. All the values in the second row of the first array are multiplied by the second row of the second array, and so on. Also, in this kind of multiplication TRUE=1 and FALSE=0. Excel will coerce all the Boolean values to 0/1 values:
| A | B | C | D | E | F | G | H | I |
|---|
| 14 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4
This now represents all the cells that match the header, AND that match the gender flag.
=SUM(
IF(ISNUMBER(SEARCH("Teacher Assessment",$A$1:$I$1)),IF(ISNUMBER(SEARCH(","&$A$2:$I$5&",",","&D$8&",")),1)*($L$2:$L$5=$B10)))
This part now looks at the column headings. If "Teacher Assessment" is in the heading, it takes the matching column from our array. If it isn't, it puts FALSE in all the rows of that column. If you put all the assessment columns together, you won't need this, just pick the right range.
Then finally, the SUM will add up all the cells in the array. At this point they will all be 1 or FALSE, and FALSE counts as 0.
So that's it. Pretty technical, and easy to miss a comma somewhere, even if you grasp the logic behind it. Hope this helps!
