counting nonblank rows

  • Thread starter Thread starter stumped
  • Start date Start date
S

stumped

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.
 
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?
 
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")
 
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)
 
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))
 
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
 
Would it matter that some of these cells contain formulas
which may or may not prompt a value?
Click to expand...

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

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