Counta function
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" wrote in message
...
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" wrote in message
...
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
|