Countif and VBA ?

The problem I have is, I use =COUNTIF formating to get an overal grade value based on a letter, so A is worth 3 B is worth 2 and so on, all the values are added up then divided by the number of subjects to get an average. We have moved Management Information Systems and the new one does not do the calculations, so I do it in excel, the trouble is that I only want to add up the values where the header row says "Effort" and not "Attainment". I think I need some VBA to work with the COUNTIF but don't know how to get there. I could remove the "Attainment" column altogether but realy need both values side by side to see progress of students. Currently I have one sheet for Attainment and one for Effort. Can any one help.

Thanks

I should re-arange the data, so it is possible to use a povit table.

Before using the pivot table, I should first add an VLookup formula to get the numbers (instead of a letter).

If you add an excel file, without confidential information, we can show it to you.

Thanks for the Help, so had a good play with it and this is my working solution to the problem, the change font type, size and colour part is not particularly elegant but works, I think would be better using a similar for next loop as first part but test for the value of A but have not quiet got my head round that yet. and I have tried to round the value of the rest in the Range(cl & i).Value = y / g to two positions after the decimal pace but just keep getting errors, so post correcting by formatting column instead. If you have any tips and trick I've missed to make it better I'm open to suggestions.

Sub WCBS_calculate_ABC_Totals_Change_Colours()

Dim i As Long
Dim x As Long
Dim y As Long 'total value of case A,B,C found
Dim g As Long 'number of times case A,B,C is found
Dim r As Long
Dim cl As String

r = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count 'finds the last column
cl = Split(Cells(1, r).Address, "$")(1) 'gets the letter equivalent of the column number
MsgBox cl
For i = Range("A" & Rows.Count).End(3)(1).Row To 4 Step -1
y = 0
g = 0
For x = 3 To r
If Cells(1, x) = "E" Then
Select Case Cells(i, x).Value
Case Is = "A"
y = y + 3
g = g + 1
Case Is = "B"
y = y + 2
g = g + 1
Case Is = "C"
y = y + 1
g = g + 1
End Select
End If
y = y
Next x
Range(cl & i).Value = y / g 'divides the total by the number of times a value is found and places it in the first column after last value in a column
Next i

Range("d1,f1,h1,j1,l1,n1,p1,r1,t1,v1,x1,z1,ab1,ad1,af1,ah1,aj1,al1,an1,ap1,ar1,at1,av1,ax1,az1,bb1,bd1,bf1,bh1,bj1,bl1,bn1,bp1,br1,bt1,bv1,bx1,bz1").EntireColumn.Select
With Selection.Font
.Name = "Calibri"
.Size = 8
.Color = -16777024
End With