Count Used Rows

[Sylvan]

I need to count the number of used rows in a range. The two problems are that I can't use VBA -- must use a formula, and I can't be sure that any particular cell in the range will have any information in it.

Right now I'm using:

=IF(COUNTA(OFFSET(A17,1,0,5000,10))=0,0,SUM(COUNTA(OFFSET(A17,1,0,5000,10))/COUNTA(OFFSET(A17,1,0,1,10))))

but this only works if all rows have the same number of used cells. I would use nested ifs, but there are ten columns and you can only use 7 nested ifs.

Any ideas?

 

[T. Valko]

I need to count the number of used rows in a range. The two problems are that I can't use VBA -- must use a formula, and I can't be sure that any particular cell in the range will have any information in it.

Right now I'm using:

=IF(COUNTA(OFFSET(A17,1,0,5000,10))=0,0,SUM(COUNTA(OFFSET(A17,1,0,5000,10))/COUNTA(OFFSET(A17,1,0,1,10))))

but this only works if all rows have the same number of used cells. I would use nested ifs, but there are ten columns and you can only use 7 nested ifs.

Any ideas?

Let's assume this is your data:

Book1
	A	B	C	D	E	F	G	H	I	J
1		30
2
3			73	54
4
5	80
6
7				44
8
9
10
11										58
12					92	69	55
13
14
15
16
17
18		92
19		82
20
Sheet1

You want to count how many rows in the range have at least one entry.

In the formula:

• Array is a defined name that refers to: ={1;1;1;1;1;1;1;1;1;1}

=SUMPRODUCT(--(MMULT(--(A1:J20<>""),Array)>0))

Array is a vertical array of ones equal to the number of columns in the range. In your range there are 10 columns so Array contains 10 ones.

If you had "many" columns you wouldn't necessarily want to do something like this:

{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;etc;}

You could use this array entered** version:

=SUM(--(MMULT(--(A1:J20<>""),TRANSPOSE(COLUMN(A1:J20)^0))>0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Sylvan]

The second of those works until I run VBA code which pastes into the range and then the range reference in the formula is replaced by #REF!.

 

[T. Valko]

The second of those works until I run VBA code which pastes into the range and then the range reference in the formula is replaced by #REF!.

Hmmm...

If you can use code to paste into the range then why this:

I can't use VBA -- must use a formula

Maybe this:

=SUM(--(MMULT(--(INDIRECT("A1:J20")<>""),TRANSPOSE(COLUMN(INDIRECT("A1:J20"))^0))>0))

Still array entered.

 

[Sylvan]

That works like a charm, thanks so much.

Just meant that I wanted the solution to be a formula rather than VBA so that the number would change automatically if data was added or deleted.

 

[T. Valko]

That works like a charm, thanks so much.

Just meant that I wanted the solution to be a formula rather than VBA so that the number would change automatically if data was added or deleted.

You're welcome. Thanks for the feedback!