• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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


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.


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
= COUNTIFS(Data[Ethnicity], IF(Ethnicity="Blank","",Ethnicity))
= COUNTIFS(Data[Gender], IF(Gender="Blank","",Gender))
= COUNTIFS(Data[Housing], IF(Housing="Blank","",Housing))
adjust automatically.