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

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

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

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