Find count of filled cells and empty cells in a column

[thepinkgeologist]

So, I have a lot of data and I need to quantify the completeness of the data.

An easy way to do this would be a quick way to get a count from the first cell with data in it to the last cell with data in it and ALSO a count of how many cells between are EMPTY.

Is there a way someone is aware of to do this?

[hoyasaxa215]

Wouldn't =COUNTA and =COUNTBLANK accomplish this?

[Ron Coderre]

NEW FORMULAS...CORRECTED AN ERROR THAT IGNORED NUMERIC ENTRIES
This formula returns the count of rows between the first non-blank Col_A cell and the last non-blank Col_A cell, inclusive:

Please Login or Register  to view this content.

This formula returns the count of blank cells between the first non-blank Col_A cell and the last non-blank Col_A cell, inclusive:

Please Login or Register  to view this content.

Note: It would be better to NOT reference an entire column. So, if you have reasonable row limit assumptions, I'd encourage you to employ them.

Is that something you can work with?

[newdoverman]

With the values (text or numbers) starting and ending somewhere in column A, I entered this formula in C1 to locate the first value. Change the size of the ranges to suit your data. Avoid full column references if at all possible.

Please Login or Register  to view this content.

Enter with Ctrl + Shift + Enter

In C2 enter this formula to locate the last value.

Please Login or Register  to view this content.

The number of blank cells between the values calculated above:

Please Login or Register  to view this content.

[Simon3]

Just for fun:

Please Login or Register  to view this content.

(Turn on Text Wrapping for that cell)