Exam grade A*

[RuthB]

Is there anyway to make Excel interpret 'A*' as 'an A followed by a star' rather than 'any string beginning with an A'?
Thanks in advance. I apologise if this has already been asked. I've searched but didn't find anything.

If you are wondering why I want to know...
I am a teacher in the UK. Our students enter some exams for which the available grades are A*, A, B, C etc, while others have exams for which the grades are numbers. I design Excel workbooks of results, which other teachers can then use for their classes. I would use COUNTIF to count the number of students with each grade. The problem is that Excel thinks that 'A*' means 'any string beginning with A' and it counts not only the 'A*'s but the 'A's as well. Until now the students have only had exams with letter grades, so I've been working round that by making the count of the top grade count 'A*'s (so it counts 'A*'s and 'A's) and then subtract the count of the next grade down so it removes the 'A's. But now exams with number grades have been introduced, and if the sheet is used for some other exam where the top grade is 9 and the next grade down is 8 then it counts the 9s and subtracts the 8s, which is not what's required at all. I could work round it again by making it count the top grade, and then if the top grade has a length of more than one character subtract off the count of the next grade down, but I am wondering if there is a more elegant way of doing it. Also, I'm worried that some teacher will accidentally enter the top grade onto the sheet as 9 with a space after it, and then that will have a length of more than one character...

 

[RoryA]

To count A* specifically, you need to escape the * by prefixing it with a tilde:

=COUNTIF(A:A,"A~*")

for example.

 

[RuthB]

THANK YOU!

I knew there'd be some simple and elegant way of doing it if I could only find out how!

 

[RoryA]

Glad to help.

By the way, if you have A* in a cell already and want to refer to that cell as the criterion, you can use SUBSTITUTE like this:

=COUNTIF(A:A,SUBSTITUTE(B1,"*","~*"))

where B1 contains A*

 

[RuthB]

You read my mind. I was just typing a question about how to do it when the criterion is in a cell when I saw you'd already added that.
Thanks again.