Counting the Number of Elements in an Array

  • Thread starter Confused_in_Houston
  • Start date

C

Confused_in_Houston

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
 
Ad

Advertisements

D

Dave Peterson

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.
 
T

T. Valko

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)),"")
 
Ad

Advertisements

C

Confused_in_Houston

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".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top