Counting some blank cells and ignoring others in a range.

**kborgers** · 09-05-2009, 03:53 AM

I would really appreciate some guidance here. Using formula rather than VB, I would like to calculate the number of blank cells that appear in a column BEFORE the first active cell but exclude any blank cells that appear after the first active cell. To elaborate, I have sheets that contain the days of each month and I need to exclude for other calculation purposes, the number of days (cells) where no entry of data has been input at the beginning of a month, NOT after the first data entry. ie. September has 30 days. The first data entry is the 10th and there are no entries (thus far) after the 10th, the result that I seek, will be 20 (although only one cell has data), being the days left in the month AFTER the first entry. I have looked at COUNT functions but cannot find an solution. Perhaps it's not possible using basic formula?

**DonkeyOte** · 09-05-2009, 03:56 AM

I don't understand... your post appears to contradict itself:

I would like to calculate the number of blank cells that appear in a column BEFORE the first active cell but exclude any blank cells that appear after the first active cell.

and then...

September has 30 days. The first data entry is the 10th and there are no entries (thus far) after the 10th, the result that I seek, will be 20 (although only one cell has data), being the days left in the month AFTER the first entry

which is it ?

**kborgers** · 09-05-2009, 04:14 AM

OK, perhaps it's the (...only one cell has data that's clouding my explanation. To simplify, I need to know how many blank cells appear in any given month before a first entry is made. Hopefully clearer? Sorry for any confusion and thanks for responding.

**DonkeyOte** · 09-05-2009, 04:17 AM

I would suggest posting a sample file as this will help clarify what you're working with exactly ... if we assume for sake of demo your values are listed in A1:A30 and you want to Countblanks appearing before first non-blank (you don't specify in non-blanks are text/numerics or either/or) then:

=IF(COUNTA(A1:A30);MATCH(TRUE;INDEX(A1:A30<>"";0);0)-1;ROWS(A1:A30))

**kborgers** · 09-05-2009, 04:27 AM

DonkeyOte, if I didn't think that your breath probably smells (most donkeys have bad breath), I would kiss you! It works an absolute treat and I cannot thank you enough! I spent hours trying to find the solution and I confess having looked at your suggestion that I was not even close. Thanks again!
BTW, the blank cells are user input for numerics.