If range is not blank, blank, or partially blank

[JoshLyman]

Excel wizards.. I would very much like a formula in B1 which reflects whether B4:E8 is either not blank, partially blank, or entirely blank. It should ideally say the following:

If not blank (i.e., all cells have been populated) = Complete
If partially blank (i.e., some cells have been populated, some haven't) = In progress
If entirely blank (i.e., no cells have been populated) = No progress

NEW Self-Assessment Report.xlsx
	A	B	C	D	E
1	Current progress
2
3	Criteria	How do we do this?	What do we do well?	How can we improve?	Self-assessed grade
4	1.1
5	1.2
6	1.3
7	1.4
8	1.5
Sheet1

I've tried doing it with SUMPRODUCT, but stacking the criteria has proven more than I can handle. Thank you in advance.

 

[Anbuselvam]

=IF(COUNTA(B4:E8)=0,"No progress",IF(COUNTA(B4:E8)=COUNT(B4:E8),"Complete","In progress"))

 

[JvdV]

I feel like this may be a bit too complicated, but it works:

Book1
	A	B	C	D	E
1	Current progress	No progress
2
3	Criteria	How do we do this?	What do we do well?	How can we improve?	Self-assessed grade
4	1.1
5	1.2
6	1.3
7	1.4
8	1.5
Sheet2

Cell Formulas
Range	Formula
B1	B1	=SWITCH(COUNTA(B4:E8),0,"No progress",ROWS(TOCOL(B4:E8)),"Complete","In progress")

If the range is static, then obviously change the `ROWS(TOCOL())` part into a static number.

 

[JoshLyman]

Worked a treat JvdV! Legend.