Another Countif Query

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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”?
 
Upvote 0
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:

ABCDEFGHI

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]English Teacher[/TD]
[TD="bgcolor: #FAFAFA"]English Teacher Target[/TD]
[TD="bgcolor: #FAFAFA"]English Teacher Assessment[/TD]
[TD="bgcolor: #FAFAFA"]Maths Teacher[/TD]
[TD="bgcolor: #FAFAFA"]Maths Teacher Target[/TD]
[TD="bgcolor: #FAFAFA"]Maths Teacher Assessment[/TD]
[TD="bgcolor: #FAFAFA"]Science Teacher[/TD]
[TD="bgcolor: #FAFAFA"]Science Teacher Target[/TD]
[TD="bgcolor: #FAFAFA"]Science Teacher Assessment[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]a[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]b-[/TD]
[TD="bgcolor: #FAFAFA"]e[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]i[/TD]
[TD="bgcolor: #FAFAFA"]s[/TD]
[TD="bgcolor: #FAFAFA"]s+[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]f[/TD]
[TD="bgcolor: #FAFAFA"]s[/TD]
[TD="bgcolor: #FAFAFA"]d+[/TD]
[TD="bgcolor: #FAFAFA"]j[/TD]
[TD="bgcolor: #FAFAFA"]m[/TD]
[TD="bgcolor: #FAFAFA"]m-[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]c[/TD]
[TD="bgcolor: #FAFAFA"]s[/TD]
[TD="bgcolor: #FAFAFA"]b+[/TD]
[TD="bgcolor: #FAFAFA"]g[/TD]
[TD="bgcolor: #FAFAFA"]m[/TD]
[TD="bgcolor: #FAFAFA"]s-[/TD]
[TD="bgcolor: #FAFAFA"]k[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]m[/TD]
[TD="bgcolor: #FAFAFA"]d-[/TD]
[TD="bgcolor: #FAFAFA"]h[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]s+[/TD]
[TD="bgcolor: #FAFAFA"]l[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]b+[/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: 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: center"]8[/TD]
[TD="align: right"]12[/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]

</tbody>
Sheet6

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet 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] "]A8[/TH]
[TD="align: left"]=SUMPRODUCT(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(A2:I5,"-",""),"+",""),"bdsm"))*ISNUMBER(SEARCH("assessment",A1:I1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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)))
 
Upvote 0
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:

ABCDEFGHI

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]English Teacher[/TD]
[TD="bgcolor: #FAFAFA"]English Teacher Target[/TD]
[TD="bgcolor: #FAFAFA"]English Teacher Assessment[/TD]
[TD="bgcolor: #FAFAFA"]Maths Teacher[/TD]
[TD="bgcolor: #FAFAFA"]Maths Teacher Target[/TD]
[TD="bgcolor: #FAFAFA"]Maths Teacher Assessment[/TD]
[TD="bgcolor: #FAFAFA"]Science Teacher[/TD]
[TD="bgcolor: #FAFAFA"]Science Teacher Target[/TD]
[TD="bgcolor: #FAFAFA"]Science Teacher Assessment[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]a[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]b-[/TD]
[TD="bgcolor: #FAFAFA"]e[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]i[/TD]
[TD="bgcolor: #FAFAFA"]s[/TD]
[TD="bgcolor: #FAFAFA"]s+[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]f[/TD]
[TD="bgcolor: #FAFAFA"]s[/TD]
[TD="bgcolor: #FAFAFA"]d+[/TD]
[TD="bgcolor: #FAFAFA"]j[/TD]
[TD="bgcolor: #FAFAFA"]m[/TD]
[TD="bgcolor: #FAFAFA"]m-[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]c[/TD]
[TD="bgcolor: #FAFAFA"]s[/TD]
[TD="bgcolor: #FAFAFA"]b+[/TD]
[TD="bgcolor: #FAFAFA"]g[/TD]
[TD="bgcolor: #FAFAFA"]m[/TD]
[TD="bgcolor: #FAFAFA"]s-[/TD]
[TD="bgcolor: #FAFAFA"]k[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]m[/TD]
[TD="bgcolor: #FAFAFA"]d-[/TD]
[TD="bgcolor: #FAFAFA"]h[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]s+[/TD]
[TD="bgcolor: #FAFAFA"]l[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]b+[/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: 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: center"]8[/TD]
[TD="align: right"]12[/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]

</tbody>
Sheet6

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A8[/TH]
[TD="align: left"]=SUMPRODUCT(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(A2:I5,"-",""),"+",""),"bdsm"))*ISNUMBER(SEARCH("assessment",A1:I1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
Did you read the bit that says control+shift+enter not just enter? You need to follow that instruction.
 
Upvote 0
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!
 
Upvote 0
Why are you using countifs? You cant. What aladin gave you works perfectly well but requires CSE entry.
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top