Tags: below, counta, created, dynamic, excel, microsoft, msdn, offset, range, rows, sheet111, sheet1a1, sheet1a1a100, software, sum, values

sum at 3 rows below dynamic range

On Microsoft » Microsoft Excel

1,647 words with 2 Comments; publish: Thu, 22 May 2008 22:45:00 GMT; (30678.13, « »)

HI

i have a dynamic range created with

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100),COUNTA(Sheet1!$1:$1))

and i want to sum the values in column c with the sum 3 rows below the end

of the dynamic range

how do i do this?

All Comments

Leave a comment...

  • 2 Comments
    • I'd just use the same criteria, but apply it to column C:

      either another defined name using this formula:

      =OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$A$1:$A$100)+3,1)

      or

      =SUM(OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$A$1:$A$100)+3,1))

      dave h wrote:

      > HI

      > i have a dynamic range created with

      > =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100),COUNTA(Sheet1!$1:$1))

      > and i want to sum the values in column c with the sum 3 rows below the end

      > of the dynamic range

      > how do i do this?

      --

      Dave Peterson

      ec35720.excel.questionfor.info.msn.com

      #1; Thu, 22 May 2008 22:46:00 GMT
    • thanks ever so much

      "Dave Peterson" <ec35720.excel.questionfor.info.msn.com> wrote in message

      news:412D2395.5D7E6AB.excel.questionfor.info.msn.com...

      > I'd just use the same criteria, but apply it to column C:

      > either another defined name using this formula:

      > =OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$A$1:$A$100)+3,1)

      > or

      > =SUM(OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$A$1:$A$100)+3,1))

      >

      >

      > dave h wrote:

      >> HI

      >> i have a dynamic range created with

      >> =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100),COUNTA(Sheet1!$1:$1))

      >> and i want to sum the values in column c with the sum 3 rows below the

      >> end

      >> of the dynamic range

      >> how do i do this?

      > --

      > Dave Peterson

      > ec35720.excel.questionfor.info.msn.com

      #2; Thu, 22 May 2008 22:47:00 GMT