Search columns and return number of cells containing "5" against colleague name

Question

What I'm looking for is a formula that will do the following: (I'll be using the first row for this example):

• Firstly check cells E:J for cells containing a 5.
• If all cells contain a 5 it will check the person's name and then add this to the box in column B against the correct name.
• If cells E:J have less than 5 in any of these cells it will disregard this row altogether.

To clarify, for Shaun it will count how many rows next to his name have 5's across the board then put the total number of rows fitting this criterion next to his name in Column B.

Answer 1

We can do this with a helper column:

Helper column, keep the name if the count of 5 equals to 5. Enter below to K2 and fill down.

=IF(COUNTIF(E2:J2,5)=5,D2,"-")

Then we are counting how many times name appears in the helper column. Enter below to B2 and fill down.

=COUNTIF($K$2:$K$11,A2)