Using a formula to determine whether multiple cells are blank or not blank

[Milos]

Hi all,

I am just trying input a formula to know if a large spread sheet with a certain task has been completed or not.

I have 100 cells that all need to be input with a value (e.g. cell is not blank) for it to be deemed to have been completed.
The cells are B4:B14,D4:D14,F4:F14,H4:H14,J4:J14,L4:L14,N4:N14,P4:P14,R4:R14,T4:T14

How can I create the formula so that one cell (let's say cell A3) will count all of the cells and if any one of the cells is blank then it will return a value of Not completed? However when all cells are completed is will return a value of Completed.

I have been trying the IF and COUNTA and ISBLANK to no avail. Although I think it is my incorrect formatting that is the biggest issue.

Thanks,

 

[FDibbins]

Maybe something like this?
=IF(COUNTA(B4:B14,D4:D14,F4:F14,H4:H14,J4:J14,L4:L14,N4:N14,P4:P14,R4:R14,T4:T14)<>110,"Not Completer","Complete")

 

[dreid1011]

Maybe something like this?
=IF(COUNTA(B4:B14,D4:D14,F4:F14,H4:H14,J4:J14,L4:L14,N4:N14,P4:P14,R4:R14,T4:T14)<>110,"Not Completer","Complete")

On the same track here, just couldn't figure out how to compact the range references.

 

[FDibbins]

I also meant to ask the OP - what is in the cells/columns between those ranges? C4:C14 etc) That might go some way to answering your "question", dreid

 

[Milos]

Thanks Ford!

 

[FDibbins]

Happy to help Milos.

So, what IS in those ranges between the ranges of interest?

 

[Milos]

In between the ranges of interest is the numbers of the questions that need to be answered so that the person inputting the information can keep track of their progress. It is a terrible layout of a spreadsheet and I may fix it soon.

 

[Aladin Akyurek]

Also, control+shift+enter, not just enter:

=IF(SUM(IF(MOD(COLUMN(B4:T14)-COLUMN(B4),2)=0,IF(LEN(B4:T14)=0,1))),"not complete","complete")

 

[FDibbins]

I have 100 cells that all need to be input with a value (e.g. cell is not blank) for it to be deemed to have been completed.
The cells are B4:B14,D4:D14,F4:F14,H4:H14,J4:J14,L4:L14,N4:N14,P4:P14,R4:R14,T4:T14

Note that those ranges equal 110 cells, not 100

Seeing as you will always have the same amount on numbers for the questions, you could maybe simplify that to this...
=IF(COUNTA(B4:T14)<>220,"Not Completer","Complete")

Note that if you really do only have 100 cells, then that would be <>200