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

#### KPBR2906

##### New Member
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.

#### Attachments

• 15.3 KB Views: 3

Like this ?

#### Attachments

• 16.4 KB Views: 8

#### bosco_yip

##### Excel Ninja
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))),"")

#### Attachments

• 20 KB Views: 3

#### KPBR2906

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

#### Peter Bartholomew

##### Well-Known Member
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
Code:
``````= COUNTIFS(Data[Ethnicity], IF(Ethnicity="Blank","",Ethnicity))
= COUNTIFS(Data[Gender], IF(Gender="Blank","",Gender))
= COUNTIFS(Data[Housing], IF(Housing="Blank","",Housing))``````

#### Attachments

• 17.6 KB Views: 2

#### Peter Bartholomew

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

#### KPBR2906

##### New Member
Thank you so much Peter. You all are unbelievable !!!!