Help with a formula please?

[Victor Delta]

I have a spreadsheet which contains rows of client data. The clients at the
top of the sheet are the active clients, and are separated from those at the
bottom (non active) by a blank row.

Can anyone help me with a formula which will give the number of active
clients i.e. by identifying the row of the topmost blank cell in column A.

Many thanks,

V

 

[Jarek Kujawa]

=MIN(IF(A1:A100="",ROW(A1:A100),""))

array-enter it = CTRL+SHIFT+ENTER

 

[Victor Delta]

=MIN(IF(A1:A100="",ROW(A1:A100),""))

array-enter it = CTRL+SHIFT+ENTER

Many thanks,

V

 

[Dave]

Hi,
or a non-array alternative:
=SUMPRODUCT(--(A1:A0100="")*ROW(A1:A100))-1
By the way, Jarek's formula also needs a minus one (-1) to give a correct
client count, since it counts the blank row, as mine does.
Regards - Dave.

 

[Victor Delta]

Hi,
or a non-array alternative:
=SUMPRODUCT(--(A1:A0100="")*ROW(A1:A100))-1
By the way, Jarek's formula also needs a minus one (-1) to give a correct
client count, since it counts the blank row, as mine does.
Regards - Dave.

Thanks. I'm actually using -2 to allow for the header row as well.

V

 

[Dave]

Hi Victor,
My formula doesn't work at all! Don't know what I was thinking. Sorry.
It sums all the blank cells in the range, not just the first one.
Dave.

 

[Ron Rosenfeld]

I have a spreadsheet which contains rows of client data. The clients at the
top of the sheet are the active clients, and are separated from those at the
bottom (non active) by a blank row.

Can anyone help me with a formula which will give the number of active
clients i.e. by identifying the row of the topmost blank cell in column A.

Many thanks,

V

This **array-entered** formula will return a value indicating the first blank
cell in the range:

=MATCH(TRUE,ISBLANK(E:E),0)

You could subtract 1 (or 2 if you have a header row in the range) to get your
number of clients.

--ron

 

[Victor Delta]

This **array-entered** formula will return a value indicating the first
blank
cell in the range:

=MATCH(TRUE,ISBLANK(E:E),0)

You could subtract 1 (or 2 if you have a header row in the range) to get
your
number of clients.

--ron

Thanks,

V