Counta function
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 function
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 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 |
Counta function
=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 |
All times are GMT +1. The time now is 12:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com