Quick Excel Help (please) :)

[slim6y]

Hi there...

I've got a sheet to count how many passes students have in certain subjects. Unfortunately we don't work on an A, B, C system, we work on N, A, M or E...

E is excellence in this case....

Ok... So I have a spreadsheet with all my grades (N, A, M or E) and I want to COUNT how many Ns, As, Ms and Es I have.

So in one class of 22 students I have a varied amounts of these - so I used the COUNTIF function.

Here is the direct copy and paste of that function:

=COUNTIF(B2:B23,"A")

That counts 6 As - perfect! It worked.

But then I went to the next cell below and typed:

=COUNTIF(B2:B23,"M")

(I actually copied and pasted the one above and changed B3 to B2 because I moved down one cell and then B24 to B23 for the same reason and I replaced the A with an M).

Unfortunately this came up with 1 as the answer - which isn't correct...

Why isn't it counting them correctly???

The cell definitely says M and it doesn't count them!

I tried all along that row and it doesn't work... Why???

Is there something wrong with me? Is there something wrong with the worksheet?

Well, let me know if you know of any reason why it won't work.

Cheers

 

[saximus]

That sounds like it should work Paul. Especially since it worked for the first case. Any chance you could post the spreadsheet (with names deleted)?

There are a few methods you can try instead. This would be the easiest - Instead of typing "M" in the function, put it in another cell (e.g. C2) then your function would be:

=COUNTIF(B2:B23,C2)

Also to stop the things changing when you copy then you can use absolute referencing. So it would be:

=COUNTIF($B$2:$B$23,C2)

 

[slim6y]

That sounds like it should work Paul. Especially since it worked for the first case. Any chance you could post the spreadsheet (with names deleted)?

There are a few methods you can try instead. This would be the easiest - Instead of typing "M" in the function, put it in another cell (e.g. C2) then your function would be:

=COUNTIF(B2:B23,C2)

Also to stop the things changing when you copy then you can use absolute referencing. So it would be:

=COUNTIF($B$2:$B$23,C2)

Thanks mate...

I just went back all old school and counted by hand!

It's stuffed up a few cells for - what seems to be - a very simple function!

I need home and All Blacks to win... Thanks tho!!

 

[saximus]

Haha ok no worries. If you need any other help let me know. I'm doing a uni subject right now on Excel and VBA so I can probably build a nice fancy spreadsheet for you for future use.

 

[slim6y]

Haha ok no worries. If you need any other help let me know. I'm doing a uni subject right now on Excel and VBA so I can probably build a nice fancy spreadsheet for you for future use.

OOOOOH!

Well... I think I could use you there

Will I be able to give you any credits towards your degree (I am still a registered Queensland Teacher)?

 

[saximus]

lol it won't go toward my degree but any practice is going to be helpful for building skills

 

[grimbeny]

My guess is that you have "M " in the cells rather than just an M or something similar.

 

[Fuscus]

My guess is that you have "M " in the cells rather than just an M or something similar.

That's my guess too, try =COUNTIF(A3:A21,"N")+COUNTIF(A3:A21,"N ") so it can count cells with trailing blanks

 

[slim6y]

My guess is that you have "M " in the cells rather than just an M or something similar.

I deleted it (thinking as you said) and when I did and replaced it with a new M - it made no difference...

 

[abnrmal91]

Excel is great if you know how to use it. Unfortunately I seem to struggle with it, took me ages the other day just to make a graph.

 

[FAY]

haha graph's are simple...and I am computer dumb as lol

 

[abnrmal91]

haha graph's are simple...and I am computer dumb as lol

Not when your using it to calculate the Young's Modulus of brass for a tensile test.

 

[slim6y]

haha graph's are simple...and I am computer dumb as lol

I'm not really trying to graph - but make a program that can continually analyse students' progress over two years in each subject... It's really to make me look good if the students are getting better (not their current form where they appear to be getting worse)....

 

[Robo1]

From the sounds of it something strange is going on. Hard to answer without seeing the spreadsheet itself though. Maybe in a new column (say C) add =IF(B2="M",1,0) to C2 and copy that down to C23. At least that way you'll be able to see which M's it's missing (the rows with a 0) and which it's counted (the rows with a 1). Hopefully from there you can figure out the difference between the cells...

 

[saximus]

Not when your using it to calculate the Young's Modulus of brass for a tensile test.

Easy stuff
Our lecturer said most people only use about 10% of Excel's capability and at the end of this course we will still only know about 50%. It's a surprisingly powerful program

 

[slim6y]

Found the problem - it was a space - but not one I intentionally put in there either.

The space keeps appearing in that cell too - and I don't know why!

Thanks for your help fellows

 

[Robo1]

Have a look at the trim function, that might solve your problem. It will require an additional column, but you can always hide that column. Not sure why it's appearing in the first place, the only thing I can suggest is that the cell may have some special formatting...?

 

[grimbeny]

have you got it set to roman numerals?

 

[slim6y]

Have a look at the trim function, that might solve your problem. It will require an additional column, but you can always hide that column. Not sure why it's appearing in the first place, the only thing I can suggest is that the cell may have some special formatting...?

I've deleted the cell several times - but it does seem to replace the space - not quite sure there. But it saved in its working fashion so I am happy enough! I'm not sure how the trim function works - so I will check that out.

have you got it set to roman numerals?

No - not roman numerals... It's all working now though. I even drew a pretty graph