Counting the Number of Elements in an Array
 

I would like to know how to write an argument that will count the number of
items in a group or an array.

Example

A1 DATA1
A2 DATA2
A3 DATA3
A4 DATA4
A5 4
A6 DATA1
A7 DATA2
A8 2
A9 DATA1
A10 DATA2
A11 DATA3
A12 3

Thanks

Counting the Number of Elements in an Array
 

Is this really an array or just a range on a worksheet?

If it's a range, you could use something like:

msgbox application.counta(activesheet.range("a1:b10"))

This will return the number of non-empty cells.

Confused_in_Houston wrote:

I would like to know how to write an argument that will count the number of
items in a group or an array.

Example

A1 DATA1
A2 DATA2
A3 DATA3
A4 DATA4
A5 4
A6 DATA1
A7 DATA2
A8 2
A9 DATA1
A10 DATA2
A11 DATA3
A12 3

Thanks

--

Dave Peterson

Counting the Number of Elements in an Array
 

Assuming the first cell in your range will *always* have an entry.

Enter this formula in B1 and copy down to 1 row past the last entry in
column A:

=IF(A1="",COUNTA(A$1:A1)-SUM(B$1:INDEX(B$1:B1,ROWS(B$1:B1)-1)),"")

--
Biff
Microsoft Excel MVP


"Confused_in_Houston" wrote in
message ...
I would like to know how to write an argument that will count the number of
items in a group or an array.

Example

A1 DATA1
A2 DATA2
A3 DATA3
A4 DATA4
A5 4
A6 DATA1
A7 DATA2
A8 2
A9 DATA1
A10 DATA2
A11 DATA3
A12 3

Thanks

Counting the Number of Elements in an Array
 

Not sure about the array part but maybe this will help....

What I want to do is to have a formula in Column B that makes an entry in
column B every time it detects a blank cell in Column A. The entry is the
count (or the number of consecutive "non-blank" cells immediately above the
blank cell in A.

So if A1 through A6 all contain numeric data, but A7 is blank - I would like
B7 to report the value "6". If A8 and A9 have data but A10 is blank - the
then formula would cause B10 to read "2".

"Dave Peterson" wrote:

Is this really an array or just a range on a worksheet?

If it's a range, you could use something like:

msgbox application.counta(activesheet.range("a1:b10"))

This will return the number of non-empty cells.

Confused_in_Houston wrote:

I would like to know how to write an argument that will count the number of
items in a group or an array.

Example

A1 DATA1
A2 DATA2
A3 DATA3
A4 DATA4
A5 4
A6 DATA1
A7 DATA2
A8 2
A9 DATA1
A10 DATA2
A11 DATA3
A12 3

Thanks

--

Dave Peterson