find first non-blank cell in column

**drfarmkid** · 12-04-2012, 11:36 PM

My spreadsheet has values in columns A and B. Column A has values only in some rows and column B has values in all rows. I need to count the number of rows between values in column A. If it helps to make sense, these are families with the family name in column A and the first names in column B. I'm trying to calculate family sizes.

After a lot of thinking about it, I figured the easiest way to do it is with this MATCH function: =MATCH(TRUE,LEN(A2:A$12)<>0,0) in C1 and dragged down. Of course I'll also add an IF function to return the value only when wanted, but for now I'm just concerned with this part. I've tried a number of different permutations of this but it always returns an error, either #VALUE or #N/A. However, click the fx button to look at the formula result, it looks right and gives the right result. I've attached an example.

Can someone explain the error, or at least point out my error? Thanks.

ETA: I titled this as finding the first non-blank cell, but now that I think about it it should be something about counting cells up to the first non-blank cell. My original idea about how to solve this required finding the first non-blank cell, but I guess this method doesn't really require that. Sorry about the not-so-accurate title.

**:) Sixthsense :)** · 12-04-2012, 11:54 PM

Fill the Column-A blank cells of Column-A using Goto method

Refer the below link for step by step instructions.
http://www.contextures.com/xlDataEntry02.html

and then use the below formula in D1 cell

Formula:

Please Login or Register  to view this content.

Drag it down.

**Teethless mama** · 12-04-2012, 11:55 PM

In C1: =IF(A1<>"",IFERROR(MATCH(TRUE,A2:A$12<>"",0),COUNTA(B1:B$12)),"")

Ctrl+Shift+Enter, not just Enter
copy down

**drfarmkid** · 12-05-2012, 01:34 AM

Okay, so the reason I was getting the error was because it is supposed to be an array function. I don't quite understand why, but okay if that's how it's supposed to be fine. I've only recently needed to use array functions for anything I've done and I'm still getting a handle on them. Thanks for the help.