I would like to return a blank cell if referenced cells are blank

111 views Asked by At

I am building a scorecard to measure compliance for agents sales calls. I have the questions listed with data validation allowing only N or Y to be input below each question. Any question that has an N below will result in a failed call as they cannot miss one part. In order to do this I have used this formula =COUNTIF(G5:J5,"N") meaning it will count the volume of N's, next to this I have put this formula =IF(K5>=1,"FAIL","PASS") however when the questions that we fill in N or Y are blank the cell next to it showing the volume of N's is always 0 until filled in resulting in the box next to that always saying 'PASS'. I would like each of these boxes to stay blank only until Y or N's are input- I hope this makes sense..

2

There are 2 answers

0
Gary's Student On

In K5 enter:

=IF(COUNTA(G5:J5)=4,COUNTIF(G5:J5,"N"),"")

and in L5 enter:

=IF(K5="","",IF(K5>=1,"FAIL","PASS"))
0
sancho.s ReinstateMonicaCellio On

Similar to Gary's Student, but a little more versatile.

K5: =IF(COUNTA(G5:J5)=COLUMNS(G5:J5),COUNTIF(G5:J5,"N"),"")
L5: =IF(K5="","",IF(K5>=1,"FAIL","PASS"))