|
Using the Count functions in dynamic range names
The usual technique of using COUNTA in dynamic range names is difficult or impossible to use if:
The range contains empty cells.
There are multiple ranges above one another on the same sheet.
The columns in the range can contain a different number of rows.
If the range can contain empty cells then COUNTA will not give the number of rows or columns in the range. Using COUNTROWS2 will give the correct number of rows.
If you have multiple ranges stacked above one another on the same sheet but separated by blank rows it is difficult to use COUNTA. Using COUNTCONTIGROWS2 can help in this situation provided that each range does not itself contain blank cells.
If the columns in the range can contain a different number of rows one approach to getting the total number of rows in the range is to use COUNTUSEDROWS2, but this may overestimate the number of rows in the range, and will not work if you have vertically stacked ranges.
A better approach is to tell COUNTROWS2 or COUNTCONTIGROWS2 to look for the last row in multiple columns (theColumns refers to a range which is a single row spanning multiple columns). In this case the functions will return the largest row count found by evaluating each column in turn.
Similar remarks apply to the COUNTCOLS2 and COUNTCONTIGCOLS2 functions.
|
