EXACT Function to ignore blank / empty cells

[JonReyno]

Hi All,

I have the following formula that works when I just look at a list of populated cells, but as soon as the list contains blank cells it then fails and returns 'In Progress'. Is there a way that I can ignore the blanks? My formula is:
=IF(AND(EXACT(G8:G11,"Complete")),"Complete",IF(AND((EXACT(G8:G11,"Not Started"))),"Not Started","In Progress")) (Entered as an array)

I have done a lot of searching but can't seem to find the right result. Basically if the list contains just a status of 'Not Started' it should be 'Not Started;', if the list contains just 'Complete' it should be 'Complete,' but for anything else it should show 'In Progress', but the range will include blank cells which need to be ignored.

Please put me out of my misery!!!

Thanks in advance
Jon

 

[AlanY]

try this

=IF(ISNUMBER(MATCH("Complete",G8:G11,0)),"Complete",IF(ISNUMBER(MATCH("Not Started",G8:G11,0)),"Not Started","In Progress"))

 

[JonReyno]

try this

=IF(ISNUMBER(MATCH("Complete",G8:G11,0)),"Complete",IF(ISNUMBER(MATCH("Not Started",G8:G11,0)),"Not Started","In Progress"))

Thanks for the reply Alan. That's not quite worked. If I have a cell that says 'Complete' in the range then the cell returns 'Complete' but it should only return 'Complete' if all the cells in the range say 'Complete' or are blank.

 

[AlanY]

so, if all 4 cells are complete, return complete, and likewise for not started (ie. all 4 cells)?
any other combinations will be in progress?

 

[JonReyno]

so, if all 4 cells are complete, return complete, and likewise for not started (ie. all 4 cells)?
any other combinations will be in progress?

Yeah, that's right. but if one of the cells is blank it needs to behave the same, so if 3 say complete and 1 is blank, then show complete and likewise for not started, else a different combination will need to show in progress.

The background behind this is that it is part of a project schedule. We have various stages in a project and the stages will comprise of tasks. If none of the tasks have been started the stage should show as not started, but if things have been put in to in progress or complete then it needs to show as in progress until all tasks have been completed and then it needs to show as complete. The is a chance that additional tasks could be added to the bottom of the list, so they need to be included as well, so the range needs to go to the bottom of the list, which is why I'm trying to rule out anything that is blank. I hope this helps explain it and I haven't confused things further.

 

[FatBoyClam]

*edited to accommodate update in #5)*
How about

=IF(COUNTIF(G8:G11,"Complete") = COUNTA(G8:G11),"Complete",IF(COUNTIF(G8:G11,"Not Started")=COUNTA(G8:G11),"Not started","In Progress"))

The Counta will return the number of cells with a value (so if there are 3 values of "complete" and one blank, it will still show complete

*Further edit*, this will return Complete if the range is all blank. To avoid this, try the version below*
=IF(AND(COUNTIF(G8:G11,"Complete") = COUNTA(G8:G11),COUNTA(G8:G11)>0),"Complete",IF(AND(COUNTIF(G8:G11,"Not Started")=COUNTA(G8:G11),COUNTA(G8:G11)>0),"Not started","In Progress"))

 

[JonReyno]

*edited to accommodate update in #5)*
How about

=IF(COUNTIF(G8:G11,"Complete") = COUNTA(G8:G11),"Complete",IF(COUNTIF(G8:G11,"Not Started")=COUNTA(G8:G11),"Not started","In Progress"))

The Counta will return the number of cells with a value (so if there are 3 values of "complete" and one blank, it will still show complete

Thanks FBC, this also isn't working as I would like. If the range is extended to rows that contain blank cells, it returns In Progress regardless of the combination of the populated cells.

 

[AlanY]

you can test this out

=IF(COUNTA(G8:G11)=0,"Not Started",IF(COUNTIF(G8:G11,"Complete")=COUNTA(G8:G11),"Complete",IF(COUNTIF(G8:G11,"Not Started")=COUNTA(G8:G11),"Not Started","In Progress")))

 

[JonReyno]

you can test this out

=IF(COUNTA(G8:G11)=0,"Not Started",IF(COUNTIF(G8:G11,"Complete")=COUNTA(G8:G11),"Complete",IF(COUNTIF(G8:G11,"Not Started")=COUNTA(G8:G11),"Not Started","In Progress")))

That's perfect!! Thank you Alan. I knew there would be a way to do it, but I couldn't get my head around it!!

 

[AlanY]

you're welcome