countif text values to represent different numerical values
 

Hi all,
I have an excel sheet with grades in ie A,B,C etc, but some have P for pass, M for merit etc.
My query is after using the countif formula to total how many grades a list of students have achieved (that is that the cells with something in have been totalled), is there a way of making some of the values added in the countif function have a different numerical weighting. eg A,B,C etc all equal 1 as they are GCSE quals, however the P and M used are from different quals some have carry a weighting of 2, others carry a value of 0.5 etc

eg- if a students has a result of 8 quals but 5 were gcses (each worth 1 qual), 2 were btec (each worth 2 quals) and 1 was a short coures(worth 0.5 quals) the total quals for this students should be 9.5 and not 8.

I have the value for each qual at the top of each indervidual column under the header (which is the name of the subject).

Can anyone help?

Regards Paul

Could you post a sample workbook with dummy data included?

I have attached a dummy copy in a zip.

It is the second last column i need help with, all the totals etc are at the bottom. I hope its self explanatory.
Cheers Paul

I think i have done it! but had to use hidden columns rather than do the whole thing in one go.
see attachement
Paul

A slightly more efficient formula would be:

=IF(COUNTIF($AX$4:$AX$6,T4)0,T$2,0)

in cell AF4 and copied down and to the right.

In order for it to work, cells AX4 to AX6 contain the letter P, M & D (one in each). These could be any cells anywhere... I just picked those because they were nearby and empty.

Nice one that works really well cheers, I must be honest i dont totally understand it all, but works and that all that counts.

I can follow it up to this bit, 0,T$2,0)
I get the T$2 bit but not sure why 0 is needed either side.

Thanks again for all you help

=IF(COUNTIF($AX$4:$AX$6,T4)0,T$2,0)
=IF(Condition,ValueIfTrue,ValueIfFalse)

The first 0 is part of the IF statement condition. The second is the 'value if false'.

So in plain English the formula says, "if a count of the number of times the value in T4 appears in AX4, AX5 and AX6 is greater than zero, show the value in T2, otherwise show zero."

Does that explain it for you?