counting nonblank rows
 

I'm trying to figure out how to integrate the number of nonblank rows within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that
does not give the correct result. Any help that you all may offer would be
greatly appreciated.

counting nonblank rows
 

Try this:

=Counta(C6:Z29)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but
that
does not give the correct result. Any help that you all may offer would
be
greatly appreciated.

counting nonblank rows
 

Thanks for the quick response. I tried that but it still is not working
properly. Would it matter that some of these cells contain formulas which
may or may not prompt a value?

"RagDyer" wrote:

Try this:

=Counta(C6:Z29)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but
that
does not give the correct result. Any help that you all may offer would
be
greatly appreciated.

counting nonblank rows
 

Hi,

RagDyer's reply counts the number of cells not the number of rows.

Don't know how you would use it in a formula but I think that you will need
to use a column to count the number of blank cells in each row and then count
the number of cells in the column < 0.

Example say in column AA:-
=COUNTA(C6:Z6)
=COUNTA(C7:Z7)
continue to row 29

Then in cell AA30 insert:-
=COUNTIF(AA6:AA29,"0")

--
Regards,

OssieMac


"stumped" wrote:

Thanks for the quick response. I tried that but it still is not working
properly. Would it matter that some of these cells contain formulas which
may or may not prompt a value?

"RagDyer" wrote:

Try this:

=Counta(C6:Z29)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but
that
does not give the correct result. Any help that you all may offer would
be
greatly appreciated.

counting nonblank rows
 

Try this array formula** :

=SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but
that
does not give the correct result. Any help that you all may offer would
be
greatly appreciated.

counting nonblank rows
 

Since the formula has to be array entered you can save a few keystokes and
just use SUM:

=SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

=SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but
that
does not give the correct result. Any help that you all may offer would
be
greatly appreciated.

counting nonblank rows
 

Thanks T,
Your suggestion seems to come close, but I keep coming up with a value of 15
when it should be 12. I was trying to follow the logic of your formula, but
I don't know that I understand arrays really well or the exponent of 0.
Could you explain a little further? Thanks



"T. Valko" wrote:

Since the formula has to be array entered you can save a few keystokes and
just use SUM:

=SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

=SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but
that
does not give the correct result. Any help that you all may offer would
be
greatly appreciated.

counting nonblank rows
 

Would it matter that some of these cells contain formulas
which may or may not prompt a value?

In other words, you have formulas that return formula blanks?

If an entire row contains formula blanks do you want that row counted?


--
Biff
Microsoft Excel MVP


"stumped" wrote in message
...
Thanks T,
Your suggestion seems to come close, but I keep coming up with a value of
15
when it should be 12. I was trying to follow the logic of your formula,
but
I don't know that I understand arrays really well or the exponent of 0.
Could you explain a little further? Thanks



"T. Valko" wrote:

Since the formula has to be array entered you can save a few keystokes
and
just use SUM:

=SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

=SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1),
but
that
does not give the correct result. Any help that you all may offer
would
be
greatly appreciated.

counting nonblank rows
 

Or, do you only want to count those rows where *every* cell is not not
blank?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Would it matter that some of these cells contain formulas
which may or may not prompt a value?

In other words, you have formulas that return formula blanks?

If an entire row contains formula blanks do you want that row counted?


--
Biff
Microsoft Excel MVP


"stumped" wrote in message
...
Thanks T,
Your suggestion seems to come close, but I keep coming up with a value of
15
when it should be 12. I was trying to follow the logic of your formula,
but
I don't know that I understand arrays really well or the exponent of 0.
Could you explain a little further? Thanks



"T. Valko" wrote:

Since the formula has to be array entered you can save a few keystokes
and
just use SUM:

=SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

=SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"stumped" wrote in message
...
I'm trying to figure out how to integrate the number of nonblank rows
within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1),
but
that
does not give the correct result. Any help that you all may offer
would
be
greatly appreciated.