Distinguishing between 'A' and 'A*' Exam Grades

Distinguishing between A and AStar V2.xlsx
Excel Question - Distinguishing between A and AStar.xlsx


I am a teacher in the UK. The current GCSE exam grading system uses letters to distinguish grades (e.g. A-G). However, they also have a result called an A-star represented by 'A*'.

I use COUNTIF functions to count the number of students on a particular grade into a table. However I can't work out a way to get it to distinguish between 'A' and 'A*'. In the example I have attached the formula for A works properly. However, the formula for 'A*' also counts those cells that include just an 'A', so the plain 'A's get counted twice.

I have previously used the "~" symbol (don't even know what its called), but I can't get it to work this time.

I hope someone can help?

Thanks

Update: I actually use a Countifs function to fulfill multiple condition (as you can see in the Version2 spreadsheet I've attached). For example I have a table for each member of staff showing how many of each grade they got. My function goes something like this COUNTIFS(ClassName,"teacher name",Grade,"grade"). Is there anyway I can combine the SUMPRODUCT formula (which works really well) with the countifs?

The ~ should work - its called a tilde

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

Slightly modified version of Sadath31's formula:

Formula:

Please Login or Register  to view this content.

Regards, TMS