2

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).

Improve this question
2

=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.

Improve this answer
2
  • COUNTBLANK accepts a range, you don't need to repeat it for each cell. =IF(COUNTBLANK(D3:D7)>2,"ELIGIBLE","INELIGIBLE") would suffice. – Drew Chapin Sep 13 '13 at 3:24
  • right, but in the example they didn't use contiguous cells, so I split it out like they would have to – PsychoData Sep 13 '13 at 3:25
1

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")
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.