In Excel (365 edition), I want to have a formula determine whether in a range of 5 cells, 3 of those cells are blank. If there are 3 cells, I want it to display INELIGIBLE, if there are fewer than 3 blank cells, I want it to display ELIGIBLE.
The values in the cells with data in them shall range from 1-6, so I'm not sure whether a COUNT function is actually appropriate.
I tried using the IF and COUNT functions similar to another question on superuser, but it returns #NAME? using the formula:
=IF(COUNT('GRADE 11 DATA'!C5,'GRADE 11 DATA'!F5,'GRADE 11 DATA'!I5,'GRADE 11 DATA'!L5,'GRADE 11 DATA'!O5)>2,ELIGIBLE,INELIGIBLE).
2 Answers
=IF((COUNTBLANK('GRADE 11 DATA'!C5)+COUNTBLANK('GRADE 11 DATA'!C5)+COUNTBLANK('GRADE 11 DATA'!C5)+COUNTBLANK('GRADE 11 DATA'!C5)+COUNTBLANK('GRADE 11 DATA'!C5))>2,"ELIGIBLE","INELIGIBLE")For your specific situation, that is.
As a more general answer.=IF((COUNTBLANK(D3)+COUNTBLANK(D4)+COUNTBLANK(D5)+COUNTBLANK(D6)+COUNTBLANK(D7))>2,"ELIGIBLE","INELIGIBLE")
Simple to do. Use COUNTBLANK instead of count.
What I'm doing is counting how many blank cells there are and adding them up. I like the phrasing of countblank to make your point, but you could also use ISBLANK to accomplish this. Something like =IF((ISBLANK(D3)+ISBLANK(D4)+ISBLANK(D5)+ISBLANK(D6)+ISBLANK(D7)>2,"ELIGIBLE","INELIGIBLE") Going from the fact that "TRUE" resolves to one, then summing those ones will work just like COUNTBLANK in this regard.
Your original formula will work if you put the text values that are returned by the IF() statement into double quotes. The #Name? error means that Excel does not recognise ELIGIBLE and INELIGIBLE as Excel commands or defined names.
If the cells contain numbers, use your formula above. I only added the quotes around the last two words.
=IF(COUNT('GRADE 11 DATA'!C5,'GRADE 11 DATA'!F5,'GRADE 11 DATA'!I5,'GRADE 11 DATA'!L5,'GRADE 11 DATA'!O5)>2,"ELIGIBLE","INELIGIBLE")Or you can use CountA() instead, which works for both:
=IF(COUNTA('GRADE 11 DATA'!C5,'GRADE 11 DATA'!F5,'GRADE 11 DATA'!I5,'GRADE 11 DATA'!L5,'GRADE 11 DATA'!O5)>2,"ELIGIBLE","INELIGIBLE")
