Another Countif Query

[Fazila]

Hello

I have a table similar to the one below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]English Teacher[/TD]
[TD]English Teacher Target[/TD]
[TD]English Teacher Assessment[/TD]
[TD]Maths Teacher[/TD]
[TD]Maths Teacher Target[/TD]
[TD]Maths Teacher Assessment[/TD]
[TD]Science Teacher[/TD]
[TD]Science Teacher Target[/TD]
[TD]Science Teacher Assessment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]b-[/TD]
[TD]e[/TD]
[TD]d[/TD]
[TD]d[/TD]
[TD]i[/TD]
[TD]s[/TD]
[TD]s+[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]d[/TD]
[TD]b[/TD]
[TD]f[/TD]
[TD]s[/TD]
[TD]d+[/TD]
[TD]j[/TD]
[TD]m[/TD]
[TD]m-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]s[/TD]
[TD]b+[/TD]
[TD]g[/TD]
[TD]m[/TD]
[TD]s-[/TD]
[TD]k[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]m[/TD]
[TD]d-[/TD]
[TD]h[/TD]
[TD]b[/TD]
[TD]s+[/TD]
[TD]l[/TD]
[TD]d[/TD]
[TD]b+[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to count the number of bs, ds, s' and ms in all the columns headed with ...Teacher Assessment.

The formula I have tried to use so far is =SUM(COUNTIF(INDEX(RawData,0,MATCH("*teacher assessment*",RawDataHeadings,0)),{"b-";"s1";"s2";"s3";"b-1";"b-2";"b-3"})) (the bit in bold highlights the many variations I need to count but only for this column).

For the other columns it will be simply B, B+, D- etch.

The result it is returning is 2 - the result should be well over 200.

Any ideas as to where I am going wrong?

Thanks

 

[Fazila]

Sorry people I really have tried to work this out but I'm really struggling:

Aladin and Steve's formulas worked great to count the overall number but now I need to count the number of cells containing teacher assessment and B and where column G contains M (this will change to take other factors into account).

So what I need is[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]NOR[/TD]
[TD]B-,S1,S2,S3,B-1,B-2,B-3[/TD]
[TD]B[/TD]
[TD]B+[/TD]
[TD]D-[/TD]
[TD]D[/TD]
[TD]D+[/TD]
[TD]S-[/TD]
[TD]S[/TD]
[TD]S+[/TD]
[/TR]
[TR]
[TD]Overall[/TD]
[TD]All Pupils[/TD]
[TD]296[/TD]
[TD]Count the number of the above values occur in columns with heading Teacher Assessment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gender[/TD]
[TD]Male[/TD]
[TD]185[/TD]
[TD]Count the number of the above values occur in columns with heading Teacher Assessment and column G equals "M"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Female[/TD]
[TD]111[/TD]
[TD]Count the number of the above values occur in columns with heading Teacher Assessment and column G equals "F"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

any thoughts?

 

[Fazila]

bump

 

[Eric W]

I'm guessing a bit at how your sheet is organized. I'm basing it on your original post. But you said that the gender is in column G, which was already used, so I put it in L. These formulas should work, but you'll have to adjust the ranges.

These formulas are just tweaking Steve's latest version. The D9 formula is just Steve's formula, but I changed it so that it reads the desired values from the header row, so you don't need to change every formula. Put the formula in D9 and drag right. It will handle letters or numbers.

The D10 formula is the same with the gender condition included. It reads the desired gender from column B, so you won't need to include that in the formula. Other conditions can be added in a similar way.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
NOR	B-,S1,S2,S3,B-1,B-2,B-3	B	B+	D-	D	D+	S-	S	S+	d,d+,s+
All Pupils
m
f

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]English Teacher[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]English Teacher Target[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]English Teacher Assessment[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Maths Teacher[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Maths Teacher Target[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Maths Teacher Assessment[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Science Teacher[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Science Teacher Target[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Science Teacher Assessment[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Gender[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b-[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]e[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]i[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]s[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]s+[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]m[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]f[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]s[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d+[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]j[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]m[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]m-[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]m[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]s[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b+[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]g[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]m[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]s-[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]k[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]f[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]m[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d-[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]h[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]s+[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]l[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b+[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]f[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Summary[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D9[/TH]
[TD="align: left"]{=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",$A$1:$I$1)),IF(ISNUMBER(SEARCH(","&$A$2:$I$5&",",","&D$8&",")),1)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazila]

Thanks for posting this Eric its not quite working yet but I've compromised and grouped all my Teacher Assessment columns together and used a sumproduct formula. If you have a minute could you break the formula down for me so I can see if where I am going wrong.

Thanks

Fazila

 

[Eric W]

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
,a,	,b,	,b-,	,e,	,d,	,d,	,i,	,s,	,s+,
,b,	,d,	,b,	,f,	,s,	,d+,	,j,	,m,	,m-,
,c,	,s,	,b+,	,g,	,m,	,s-,	,k,	,b,	,1,
,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>
[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

</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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

</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.

	M

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]FALSE[/TD]

</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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</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!

 

[Fazila]

That makes sense Eric and thank you for helping me - will be looking at the document again so thought it best to start getting my head around the whole process

 

[Fazila]

Another countif query

I am trying use a countifs formula with index match but keep getting #VALUE . The formula I am using is

=countifs(index(rawdata,0,match(a2,rawdataheadings,0)),"<>"&"",KS2A,">=4")/n2

In essence, if the column matching a2 has any data then to count where entries in column KS2A has a value of 4 or above.

If I change to countif the formula works but the countifs won't. Any thoughts?

Thanks

 

[Aladin Akyurek]

Is index pointing to a text range?

And is

=ROWS(rawdata)=ROWS(KS2A)

TRUE?

 

[Fazila]

Thanks Aladin yes that was the issue the KS2A was within the same range as the raw data