Use formula to count blanks in columns in a worksheet that grows every day

[KPBR2906]

I have a spreadsheet that grows every day. I need to get a count of predefined set of values. Some of the values in columns are blanks - including blank in the last row. I have used the COUNTIF function to calculate the count for non-blank values for each dimension that I am getting the count for.

What formula/function can i use so that I don't have to update the range every day for counting the blanks.

 

[pecoflyer]

Like this ?

 

[bosco_yip]

Maybe,

In H5, formula copied down :

=IFERROR(1/(1/COUNTIFS(A:A,"<>",CHOOSE(MATCH(LOOKUP(1,0/(G$2:G4="Type"),G$1:G3),$C$1:$E$1,0),C:C,D:D,E:E),IF(G5="Blank","",G5))),"")

 

[KPBR2906]

Thank you, pecoflyer and bosco_yip. Have two approaches now. Appreciate the prompt response.

 

[Peter Bartholomew]

I am clearly not switched on to the ways of spreadsheets! Why reference cell C1000? It is not part of the model and, most likely, never will be. If you use an Excel Table, it grows with the data; that is its purpose. Then formulas of the form

= COUNTIFS(Data[Ethnicity], IF(Ethnicity="Blank","",Ethnicity))
= COUNTIFS(Data[Gender], IF(Gender="Blank","",Gender))
= COUNTIFS(Data[Housing], IF(Housing="Blank","",Housing))

adjust automatically.

 

[Peter Bartholomew]

Having looked at @bosco_yip 's solution, I couldn't give up without trying to complete the challenge with a single formula!

 

[KPBR2906]

Thank you so much Peter. You all are unbelievable !!!!