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]

I take it back Aladin's formula did work only problem is it doesn't work where the value has + or -. I have added the ~symbol before the + and - but still no luck any idea how I can get around that?

Thanks again

 

[steve the fish]

What does doesnt work mean? What is the formula you use and what doesnt work about it?

 

[Fazila]

Thanks Steve I appreciate your time with this.

The formula works where I only have to count the number of Bs entered, however, for the column where I have to count "B-" or "B+" it is returning 0. I'm assuming this has to do with the fact that there is a "-" and a "+".

How can I make the formula count the values "B-" and "B+" as well?

 

[steve the fish]

The original formula aladin provided just counts "B", "B-" or "B+" because it counts anything that starts with "B". Do you just want to count "B-" but ignore "B"?

 

[Fazila]

Yep, basically I need to populate this table

[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]Summary[/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]Count the number of the above values occur in columns with heading Teacher Assessment[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So for the first column I will need to count B-, S1 etc. For the second column I will need to count the Bs, for the third the B+' etc.

I will also need to count the number of 1s where the column headings contain the word "Progress" and again the formula is turning a 0 value I'm assuming because it is a numerical value rather than text.

Hope the above make sense and again thanks for your time

 

[steve the fish]

See if this works:

=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",A1:I1)),IF(ISNUMBER(MATCH(LEFT(A2:I5,2),{"b-","s1","s2","s3"},0)),1)))

Notice the difference in the LEFT formula.

 

[Fazila]

See if this works:

=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",A1:I1)),IF(ISNUMBER(MATCH(LEFT(A2:I5,2),{"b-","s1","s2","s3"},0)),1)))

Notice the difference in the LEFT formula.

Thank you so much Steve that worked!

And I know I'm taking advantage here a little but any ideas on how I can change the formula to count numbers? One of the columns I have to populate will be counting the number 1, then 2, then 3 etc.

 

[Fazila]

And is it possible to add other conditions to the formula i.e. if I want to count the number of students who are male and have got B- etc?

 

[steve the fish]

=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",A1:I1)),IF(ISNUMBER(MATCH(A2:I5,{1,2,3,4},0)),1)))

You can add other conditions but it depends on the data.

 

[Fazila]

=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",A1:I1)),IF(ISNUMBER(MATCH(A2:I5,{1,2,3,4},0)),1)))

You can add other conditions but it depends on the data.

Thanks Steve you are an absolute genius.

The other conditions would be letters so I would need to calculate the number of males who meet the above criteria, then females, students who are gifted and talented or SEN etc

so it would be count if named range gender = M and teacher assessment columns equals B