Calculate Yes/No percentages

[Flierguy]

Hi all - I’m trying to create a formula that will determine the pass/fail percentages for individual students. I know I can use the COUNTIF and COUNTA functions, but how can I create a formula that will take only calculate the pass/fail percentage for Jeremy? (See below)

Table 1 - Jeremy - John - David - Jeremy
Table 2 - Pass - Fail - Pass - Fail

I know it would be something like If Table 1 = “Jeremy”, Then COUNTIF/COUNTA, but I’m struggling with actually coming up with the formula.

[FDibbins]

Try COUNTIFS()....
=countifs(range-with-name,"Jeremy", range-with-pass/fail,"Pass")

Alternatively, you could put the Name and Pass or Fail in their own cells and then reference those cells, to make it more dynamic.
=countifs(range-with-name,A1, range-with-pass/fail,B1)

[Flierguy]

Thanks! So if I wanted to calculate the percentage then I would do:

COUNTIFS()....
=countifs(range-with-name,"Jeremy", range-with-pass/fail,"Pass")/counta(range-with-name,”Jeremy”, range-with-pass-fail)?

[FDibbins]

Not quite, counta doesnt take arguments, so...
=countifs(range-with-name,"Jeremy", range-with-pass/fail,"Pass")/countifs(range-with-name,"Jeremy")

That would give just Jeremy passes

[Flierguy]

That works, thanks! Is there another function I can use besides COUNTA, in case Jeremy has a blank field?

[FDibbins]

If he has a blank, then he didnt score...therefor it wouldnt count towards his %

[Flierguy]

Oh okay, thanks!

[FDibbins]

no problem