If & countifs

[Mr.Jay]

Hi,

Am having difficulties in getting the accurate data for my table.
I have scores for my students but i want to count only the C's and D's in my table for each student.

Next is I want to see the fail accuracy percentange of each student based on the total count of all A's and B's divided by the total number of quizzes the student took minus the total number of days they are absent.

below is the formula that am using. can someone help me improve my formula? Am currently using excel 2007.

Thanks!


<TABLE style="WIDTH: 337pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=449><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 6729" width=184><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 97pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; HEIGHT: 12.75pt; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=17 width=129>Student Name</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 138pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=184>Scores</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 102pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=136></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Adam</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>Eve</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Steve</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>Tyler</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Edna</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>Pia</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Tyler</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>Edna</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Pia</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>Adam</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Eve</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>Steve</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Tyler</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>Edna</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>Pia</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; HEIGHT: 12.75pt; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl68 height=17>Student</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl68>Total Failed Scores (C&D)</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl68>FAIL ACCURACY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl67 height=17>Adam</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>9</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl69 align=right>-200%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl67 height=17>Edna</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>9</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl69 align=right>-200%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl67 height=17>Eve</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>9</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl69 align=right>-200%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl67 height=17>Pia</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>9</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl69 align=right>-200%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl67 height=17>Steve</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>9</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl69 align=right>-200%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl67 height=17>Tyler</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl69 align=right>-200%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl66 height=17 colSpan=3>=IF([Student]="","",COUNTIF(Table7[Scores],"C")+COUNTIF(Table7[Scores],"D"))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl66 height=17 colSpan=3>=(COUNTIF(Table7[Scores],"A")+COUNTIF(Table7[Scores],"B"))/(COUNTIF(Table7[Scores],[Total Failed Scores (C&D)])-COUNTIF(Table7[Scores],"N/A"))</TD></TR></TBODY></TABLE>

 

[Yahya]

try this formula for "Total Failed Scores (C&D)"

=IF([@Student]="","",SUMPRODUCT((Table7[Student Name]=[@Student])*(Table7[Scores]={"C","D"})))

 

[Mr.Jay]

Didnt work bro...

 

[Yahya]

try this please

=IF([Student]="","",SUMPRODUCT((Table7[Student Name]=Table2[[#This Row],[Student]])*(Table7[Scores]={"C","D"})))

 

[Mr.Jay]

Still giving me #N/A...

 

[Yahya]

why ??
this is your data

Excel Workbook
	A	B	C	D	E	F	G
1
2	Student Name	Scores			Student	Total Failed Scores (C&D)	FAIL ACCURACY
3	Adam	A			Adam	1
4	Eve	B			Edna	3
5	Steve	B			Eve	1
6	Tyler	C			Pia	2
7	Edna	C			Steve	0
8	Pia	D			Tyler	2
9	Tyler	N/A
10	Edna	D
11	Pia	N/A
12	Adam	D
13	Eve	C
14	Steve	B
15	Tyler	C
16	Edna	C
17	Pia	D
Sheet3

Excel 2007

Cell Formulas
Range	Formula
F3	=IF([Student]="","",SUMPRODUCT((Table7[Student Name]=Table2[[#This Row],[Student]])*(Table7[Scores]={"C","D"})))
F4	=IF([Student]="","",SUMPRODUCT((Table7[Student Name]=Table2[[#This Row],[Student]])*(Table7[Scores]={"C","D"})))
F5	=IF([Student]="","",SUMPRODUCT((Table7[Student Name]=Table2[[#This Row],[Student]])*(Table7[Scores]={"C","D"})))
F6	=IF([Student]="","",SUMPRODUCT((Table7[Student Name]=Table2[[#This Row],[Student]])*(Table7[Scores]={"C","D"})))
F7	=IF([Student]="","",SUMPRODUCT((Table7[Student Name]=Table2[[#This Row],[Student]])*(Table7[Scores]={"C","D"})))
F8	=IF([Student]="","",SUMPRODUCT((Table7[Student Name]=Table2[[#This Row],[Student]])*(Table7[Scores]={"C","D"})))

 

[Mr.Jay]

Thanks bro!
It works now.