CODEWITHSUNDEEP
excel
Krishna Kumar
Krishna Kumar

Reputation: 125

IF condition in excel

I'm using some an formula with if condition to know certain cells in the excel sheet are filled up or not. Basically what I need is, when the cells I listed have some value then a message will show 'COMPLETE' else 'NOT COMPLETE'. I'm using the below formula and it is working fine:

=IF(
OR(ISBLANK(C6),ISBLANK(C7),ISBLANK(C9),ISBLANK(C10),ISBLANK(C11),ISBLANK(C12),
   ISBLANK(C17),ISBLANK(C18),ISBLANK(C19),ISBLANK(C20),ISBLANK(C21),ISBLANK(C22),
   ISBLANK(C23),ISBLANK(B16),ISBLANK(C25),ISBLANK(C26),ISBLANK(C27),ISBLANK(C28),
   ISBLANK(C29),ISBLANK(C30),ISBLANK(C31),ISBLANK(C32),ISBLANK(C33),ISBLANK(C34),
   ISBLANK(C35),ISBLANK(C36),ISBLANK(C37),ISBLANK(C38),ISBLANK(C39)), 
"NOT COMPLETE","COMPLETE")

the issue is now if I try to add more such cells then I'm getting an error message "More arguments have been specified for this function than are allowed in the current file format".

Please help me out to add more cells to my condition. Thanks in advance, Krishna

Upvotes: 1

Views: 1003

Answers (4)

Simon1979
Simon1979

Reputation: 2108

Would the below do the same for you? It counts the blank cells in a range and if the count is greater than 1 then returns not complete

=IF(COUNTBLANK(C6:C39)>0,"NOT COMPLETE","COMPLETE")

You can select numerous ranges such as:

=IF(SUM(COUNTBLANK(INDIRECT({"C6:C23","B16","C25:C39"})))>0,"NOT COMPLETE","COMPLETE")

EDIT

Not sure what version of Excel COUNTBLANK came in, alternatively you could use COUNTIF, which was definitely in 2003:

=IF(SUM(COUNTIF(INDIRECT({"C6:C23","B16","C25:C39"}),""))>0,"NOT COMPLETE","COMPLETE")

This counts cells equal to "" (blank) and returns NOT COMPLETE if greater than 0.

Upvotes: 5

Tim
Tim

Reputation: 28520

It sounds like you are using a version of Excel prior to 2007 - the Excel OR function is limited to 30 expressions in earlier versions (255 in 2007 and later).

I don't have Excel 2007 available, but try using a nested If statement, like this. Assume you have 50 cells - put the first 30 in the first OR, and the remaining 20 in the second OR.

IF(OR(<expressions>),"NOT COMPLETE",IF(OR(<expressions>),"NOT COMPLETE","COMPLETE")

The first (outer) IF expression will evaluate the first 30 cells to see if any are blank. If any are blank, the evaluation returns True and will print "NOT COMPLETE".

If none of them are blank, the evaluation will return False and the second (inner) OR expression will be evaluated.

If any of the cells in the second OR are blank, it will evaluate to True and print "NOT COMPLETE".

If none of the cells in the second OR are blank, then it will evaluate to True and print "COMPLETE".

You can nest up to 7 IF statements.

Upvotes: 1

Eric
Eric

Reputation: 19863

A cheap hack would be to use an intermediate cell to compute smaller ranges of cells.

Lets say you use cell z1 to store the result of your first IF for C6 - C3

put in cell z1
=if (OR( ISBLANK(6)...,ISBLANK(39)), 1, 0)
and in cell z2
=if (OR( ISBLANK(40)...,ISBLANK(69)), 1, 0)

then in target cell ( I don't remember how to check for equality)
if (OR z1==1, z2==1, "NOT COMPLETE", "COMPLETE")

Upvotes: 0

Dale M
Dale M

Reputation: 2473

=IF(Sum(IsBlank(C6),IsBlank(C7), etc.)=0,"complete","not complete")

False=0 and True=1 in excel

Upvotes: 0

Related Questions