I have a variable that needs to count how many tests a representative completed, but only count if the test is passed within 3 tries. However, multiple representatives (Up to 4) can take the same test together. I need to count how many tests each representative has passed individually.

Test#,REP1,REP2,REP3,REP4,Test1,Test2,Test3
12,Adam,,,,Fail,,,
13,Chad,Adam,,,Pass,,,
14,Chris,Chad,Adam,,Fail,Pass,,
15,Jill,,,,Pass,,,
16,Mike,Chris,,,Fail,Fail,Pass,

I have used the COUNTIFS on all the combined conditions I can think of, one solution is to create a MASSIVE COUNTIFS+COUNTIFS+COUNTIFS+COUNTIFS function, but that's not a good answer... Is this problem something that is advanced? I'm currently putting the information into an ACCESS database, to see if I can fiddle with the data there, but I can't get the results I'm looking for there either...

If I wasn't clear enough, I'd like to write the scenario:
Adam worked on test#12 alone, Adam Failed the first time(And didn't try again).
Adam and Chad both worked on test#13 together, They passed the first time.
Chris, Chad, and Adam worked on test#14 together, they failed once, but passed the second time.

How many tests did Adam take, that he passed. How many tests did Adam fail? How many tests did Chad take, that he passed/failed?
etc... and on to 7000+ tests and 40 Representatives.

But the limit of people taking the test is 4, and the amount of attempts at the test is limited to 3, and no one else can take the test, the test is unique to the first group/individual to take it.

I'm thinking my schema of the data is what's tripping me up, or maybe it's something else altogether, I'd really appreciate a lesson here, I know some C++ syntax and Classes/structs/inheritance stuff, so I'm open to writing a macro(I've never tried macros in Excel yet), but this is burning my brain!

PS This is not a school assignment and I absolutely have to use Excel/Access