Another Countif Query

[Fazila]

Hello

I have a table similar to the one below:

English Teacher	English Teacher Target	English Teacher Assessment	Maths Teacher	Maths Teacher Target	Maths Teacher Assessment	Science Teacher	Science Teacher Target	Science Teacher Assessment
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	b
d	m	d-	h	b	s+	l	d	b+

<tbody>
</tbody>

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]

any ideas anyone??

 

[Fazila]

So the problem is it is only counting from the first column how can I make the formula count from every column that contains the words “teacher assessment”?

 

[Eric W]

Is it true that you don't care if there is a + or -, just if the b,d,s,m is in the cell? Then maybe:

	A	B	C	D	E	F	G	H	I
1	English Teacher	English Teacher Target	English Teacher Assessment	Maths Teacher	Maths Teacher Target	Maths Teacher Assessment	Science Teacher	Science Teacher Target	Science Teacher Assessment
2	a	b	b-	e	d	d	i	s	s+
3	b	d	b	f	s	d+	j	m	m-
4	c	s	b+	g	m	s-	k	b	b
5	d	m	d-	h	b	s+	l	d	b+
6
7
8	12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula A8 =SUMPRODUCT(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(A2:I5,"-",""),"+",""),"bdsm"))*ISNUMBER(SEARCH("assessment",A1:I1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",A1:I1)),IF(ISNUMBER(MATCH(LEFT(A2:I5),{"b","d","s","m"},0)),1)))

 

[Fazila]

Is it true that you don't care if there is a + or -, just if the b,d,s,m is in the cell? Then maybe:

	A	B	C	D	E	F	G	H	I
1	English Teacher	English Teacher Target	English Teacher Assessment	Maths Teacher	Maths Teacher Target	Maths Teacher Assessment	Science Teacher	Science Teacher Target	Science Teacher Assessment
2	a	b	b-	e	d	d	i	s	s+
3	b	d	b	f	s	d+	j	m	m-
4	c	s	b+	g	m	s-	k	b	b
5	d	m	d-	h	b	s+	l	d	b+
6
7
8	12

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula A8 =SUMPRODUCT(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(A2:I5,"-",""),"+",""),"bdsm"))*ISNUMBER(SEARCH("assessment",A1:I1))) <tbody> </tbody>

<tbody>
</tbody>

Unfortunately I need to consider the + and -. So I will need to count the number of B- in all the columns which contain Teacher Assessment in the column, then all the Bs, B+s etc.

 

[Fazila]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("Teacher Assessment",A1:I1)),IF(ISNUMBER(MATCH(LEFT(A2:I5),{"b","d","s","m"},0)),1)))

Thanks for responding Aladin unfortunately it returns 0

 

[steve the fish]

Did you read the bit that says control+shift+enter not just enter? You need to follow that instruction.

 

[Fazila]

Did you read the bit that says control+shift+enter not just enter? You need to follow that instruction.

Yes I did but still no luck

I've tried to simplify and try using countifs i.e.:

=countifs(rawdataheadings,"*Teacher Assessment*",rawdata,E1)

But get a #Value ! response

If I split them apart and do

=countif(rawdataheadings,"*Teacher Assessment*") I get the result 30
=countif(rawdata,"B") I get the result 894

But combined I get a Value used in the formula is of the wrong data type!

 

[steve the fish]

Why are you using countifs? You cant. What aladin gave you works perfectly well but requires CSE entry.