Another Countif Query

F

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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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
Eric W said:
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]
Click to expand...

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
steve the fish said:
Did you read the bit that says control+shift+enter not just enter? You need to follow that instruction.
Click to expand...

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
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
144
GMC The Macro Man
GMC The Macro Man
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
390
Jagadeshrao
J
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
191
Fluff
Fluff
GMC The Macro Man
  • Question Question
Clear Data without VBA
2
Replies
12
Views
4K
mumps
mumps
GMC The Macro Man
  • Solved
If Then Count calculation on Questionnaire
Replies
7
Views
540
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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