> =COUNTA(OFFSET(abc!C$1,(ROWS(A$1:A1)-1)*60,,10))
> You can simplify the suggestion above in a couple of ways:
> =COUNTA(OFFSET(abc!C$1,(ROW(A1)-1)*60,,10))
Yeah, that works but if a new row 1 is inserted it'll break. Since we don't
know if that's a possibility my preference is to always use ROWS and not
have to worry about it.
Same reasoning also applies to using ROW() and/or COLUMN() with no argument.
--
Biff
Microsoft Excel MVP
"Shane Devenshire" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi folks,
>
> =COUNTA(OFFSET(abc!C$1,(ROWS(A$1:A1)-1)*60,,10))
> You can simplify the suggestion above in a couple of ways:
>
> =COUNTA(OFFSET(abc!C$1,(ROW(A1)-1)*60,,10))
>
> and then if you give the range abc!C1:C10 the name A you can make the
> following changes:
>
> =COUNTA(OFFSET(A,(ROW(A1)-1)*60,))
>
> Cheers,
> Shane
>
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>>How do I copy a formula of "COUNTA" for a range to the next
>>>row in which the row no is jumping to row 50?
>>>=counta(abc!c1:c10)
>>>=counta(abc!c61:c70)
>>
>> Your interval is 60 rows. Assume you're entering this formula in cell A1
>> and copying down:
>>
>> =COUNTA(OFFSET(abc!C$1,(ROWS(A$1:A1)-1)*60,,10))
>>
>> A1 = COUNTA(abc!C1:C10)
>> A2 = COUNTA(abc!C61:C70)
>> A3 = COUNTA(abc!C121:C130)
>> A4 = COUNTA(abc!C181:C190)
>> etc
>> etc
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Help" <(E-Mail Removed)> wrote in message
>> news:9B444525-C941-4B91-9636-(E-Mail Removed)...
>>> How do I copy a formula of "COUNTA" for a range to the next row in which
>>> the
>>> row no is jumping to row 50?
>>> For example, i want to count the no appear in the other sheet where
>>> =counta(abc!c1:c10) and returns value is 2. However when i copy the
>>> formula
>>> to the next row of the same sheet, the formula will be read as
>>> =counta(abc!c2:c11) but what i want is =counta(abc!c61:c70).
>>>
>>> Thanks
>>>
>>
>>
|
Similar Threads
