counting nonblank rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stumped - 05 Mar 2008 01:27 GMT

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.

Reply to this Message

RagDyer - 05 Mar 2008 01:35 GMT

Try this:

=Counta(C6:Z29)

Signature

HTH,

RD

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

> I'm trying to figure out how to integrate the number of nonblank rows
> within
[quoted text clipped - 3 lines]
> be
> greatly appreciated.

Reply to this Message

stumped - 05 Mar 2008 02:00 GMT

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?

> Try this:
>
[quoted text clipped - 6 lines]
> > be
> > greatly appreciated.

Reply to this Message

OssieMac - 05 Mar 2008 02:12 GMT

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

Signature

Regards,

OssieMac

> 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
[quoted text clipped - 10 lines]
> > > be
> > > greatly appreciated.

Reply to this Message

T. Valko - 05 Mar 2008 02:48 GMT

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)

Signature

Biff
Microsoft Excel MVP

> I'm trying to figure out how to integrate the number of nonblank rows
> within
[quoted text clipped - 3 lines]
> be
> greatly appreciated.

Reply to this Message

T. Valko - 05 Mar 2008 05:16 GMT

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))

Signature

Biff
Microsoft Excel MVP

> Try this array formula** :
>
[quoted text clipped - 10 lines]
>> be
>> greatly appreciated.

Reply to this Message

stumped - 05 Mar 2008 14:59 GMT

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

> Since the formula has to be array entered you can save a few keystokes and
> just use SUM:
[quoted text clipped - 15 lines]
> >> be
> >> greatly appreciated.

Reply to this Message

T. Valko - 05 Mar 2008 18:35 GMT

>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?

Signature

Biff
Microsoft Excel MVP

> Thanks T,
> Your suggestion seems to come close, but I keep coming up with a value of
[quoted text clipped - 26 lines]
>> >> be
>> >> greatly appreciated.

Reply to this Message

T. Valko - 05 Mar 2008 18:46 GMT

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

Signature

Biff
Microsoft Excel MVP

> >Would it matter that some of these cells contain formulas
>>which may or may not prompt a value?
[quoted text clipped - 33 lines]
>>> >> be
>>> >> greatly appreciated.

Reply to this Message

Rate this thread: