counta - from 7th cell of a column to the last cell of that column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
Would you show me how to use counta to find the height of a range. The
range is from the 7th row of a column to the last cell of the same column
(the column may not be the longest).

Thanks,
 
Sounds like you're trying to create a dynamic range. What type of data is in
this column? Text? Numbers? Both? Are there any empty cells or blanks
(formula blanks: "") *within* the range? If there are formula blanks at the
end of the range do you want them to be counted?

Biff
 
Biff,

I like this question. I have as many questions to the poster as well. I attempted to answer and before I pressed "send," I saw your post.

Now, I submit my post for your approval. Please help me learn too.

**********************************************************************************
Jeff,

Do you have hidden values? Do you have blanks in between? If yes, do you want to count them?

The following is to give you an idea. You may have to adjust accordingly.

To count a range that contains text and numbers with no blanks in between, you can try this formula:

=SUBTOTAL(3,C:C)-6

C is the column you want to count.
The 3 is for the COUNTA function. You may need to use 103 for COUNTA if you want to ignore hidden values. See Help (SUBTOTAL) for details. Mind you Help doesn't discuss about manual hide and auto filter hide in details. Don't worry if you don't have any hidden rows.

-6 because you want to start from row 7. This is provided rows 1-6 have data.
If they are blank, do NOT subtract 6 because blank cells will not be counted anyway.

Once again, this is just an idea. I don't know your exact criteria. Also, I hope I have understood your post correctly.

Also, Status Bar AutoCalc may be another alternative, depending on what you want.

Epinn


Sounds like you're trying to create a dynamic range. What type of data is in
this column? Text? Numbers? Both? Are there any empty cells or blanks
(formula blanks: "") *within* the range? If there are formula blanks at the
end of the range do you want them to be counted?

Biff
 
Hi Jeff

It is usually better to keep your question in the same thread rather
than start a new one.

Debra posted an answer to your original question
=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B)
-COUNTA(Sheet1!$B$1:$B$6),1)

Alternatively, you could just start the COUNTA from row 7 down to 65536
(or shorter if you know your data range is not going to extend for the
whole of the column.

=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$7:$B$65536),1)

In your original thread, you did ask about making the formula generic.
If you were meaning that the range width might vary as well as the
height, then in place of the 1 at the end of the formula you could have
another COUNTA to calculate the width.

=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$7:$B$65536),COUNTA($B$7:$IV$7))
 
Thank you all for answering... those are very informative info. Thanks Roger
to pick out my other post. and Yes... that's exactly what I wanted to do. I
have name ranges for each column, they all start at row7 and end on same row
(data will be kept adding into spreadsheet therefore i don't/can't tell the
exact rows they end at).
the biggest problem is the "header rows" (data from row 1 to row 6 some
maybe blank and some maybe not) therefore, I can't just use counta for entire
row - counta for header rows.

Thanks,
 
Back
Top