Adding countifs incorrectly

[mtaylor]

Hi there,

I am completing data analysis for the school and one of the subjects is still using letters to record grades (A* - E)

I have several countifs in a formula which counts a certain letter based upon the class the student is in.

Here is what I currently have:

=(COUNTIFS('Grade 10'!$C$3:$C$100,"10-A",'Grade 10'!$CX$3:$CX$100,"C"))+(COUNTIFS('Grade 10'!$C$3:$C$100,"10-A",'Grade 10'!$CX$3:$CX$100,"B"))+(COUNTIFS('Grade 10'!$C$3:$C$100,"10-A",'Grade 10'!$CX$3:$CX$100,"A"))+(COUNTIFS('Grade 10'!$C$3:$C$100,"10-A",'Grade 10'!$CX$3:$CX$100,"A*"))+(COUNTIFS('Grade 10'!$C$3:$C$100,"10-A",'Grade 10'!$CX$3:$CX$100,"D"))

Column C is the class hence looking up 10-A
Column CX is the Term 3 Grade which I'm counting from. It would appear all is correct, however I end up with more students than are actually in the class.

If any more info is required, please let me know - thank you

Matt

 

[jasonb75]

The problem is here
(COUNTIFS('Grade 10'!$C$3:$C$100,"10-A",'Grade 10'!$CX$3:$CX$100,"A*"))

The * character is a wildcard so it will be counting "A" as well as "A*" (it will count anything that starts with "A")

You can either use your existing formula and remove the section that only counts "A" so that the "A*" part counts both, or change the criteria in the "A*" section to "A~*" so that it looks for the * character instead of treating it as a wildcard.

 

[mtaylor]

The problem is here
(COUNTIFS('Grade 10'!$C$3:$C$100,"10-A",'Grade 10'!$CX$3:$CX$100,"A*"))

The * character is a wildcard so it will be counting "A" as well as "A*" (it will count anything that starts with "A")

You can either use your existing formula and remove the section that only counts "A" so that the "A*" part counts both, or change the criteria in the "A*" section to "A~*" so that it looks for the * character instead of treating it as a wildcard.

Hero - many thanks