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


what i'm looking formula following: (i'll using first row example):

  • firstly check cells e:j cells containing 5.
  • if cells contain 5 check person's name , add box in column b against correct name.
  • if cells e:j have less 5 in of these cells disregard row altogether.

to clarify, shaun count how many rows next name have 5's across board put total number of rows fitting criterion next name in column b.

enter image description here

we can helper column:

helper column, keep name if count of 5 equals 5. enter below k2 , fill down.

=if(countif(e2:j2,5)=5,d2,"-") 

then counting how many times name appears in helper column. enter below b2 , fill down.

=countif($k$2:$k$11,a2) 

enter image description here


Comments