Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Using SumIF worksheet function to the end of the column

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using SumIF worksheet function to the end of the column

    How can I rewrite this SumIF =SUMIF(A$2:A$1760,AY2,U$2:U$1760)
    so that the A$1760 & U$2:U$1760 is NOT hard coded, but sets the range from A2 to the last used cell in Column A and Column U.

    Thanks.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    First assign the row number of the last used cell in column A to a variable:

    Code:
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Then the formula string would be "=SUMIF(A$2:A$" & LastRow & ",AY2,U$2:U$" & LastRow & ")".
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Jun 2012
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    Hi,

    You can define the data set (in column A & u) as 2 different tables.

    Steps to insert table: 1. Select the data on col A
    2. Click Insert -> Table option`
    Follow the same steps for col U also.

    Finally select the range in sumif function and you can see the table name got selected.
    Now when a new row is added to the table , your formula will be automatically updated with the new record with the help of table.

  4. #4
    New Member
    Join Date
    Jan 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    Quote Originally Posted by Andrew Poulsom View Post
    First assign the row number of the last used cell in column A to a variable:

    Code:
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Then the formula string would be "=SUMIF(A$2:A$" & LastRow & ",AY2,U$2:U$" & LastRow & ")".
    Thanks Andrew:
    However, I DO NOT want to use VBA. I'm trying to get this to work directly on a worksheet . I'm using Excel 2003 on Win XP.

    Thanks.

  5. #5
    New Member
    Join Date
    Jan 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    Thanks for pointing me in this direction.
    I forgot about using Dynamic Named Ranges. see: Dynamic Named Ranges That Expand and Contract Automatically in Your Excel Spreadsheet
    If my A range is Alot and the U Range is Ulot, how do I use them in the SunIf formula on the worksheet.
    Trying to re-write this using the named ranges. "=SUMIF(A$2:A$" & LastRow & ",AY2,U$2:U$" & LastRow & ")".

    Thanks

    Quote Originally Posted by anand_babu123 View Post
    Hi,

    You can define the data set (in column A & u) as 2 different tables.

    Steps to insert table: 1. Select the data on col A
    2. Click Insert -> Table option`
    Follow the same steps for col U also.

    Finally select the range in sumif function and you can see the table name got selected.
    Now when a new row is added to the table , your formula will be automatically updated with the new record with the help of table.

  6. #6
    Board Regular
    Join Date
    Jun 2012
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    Hi,

    Try this..

    =SUMIF(Alot[[#All],[Column1]],AY2,Ulot[[#All],[Column1]])
    Thanks,

  7. #7
    Board Regular
    Join Date
    Jun 2012
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    Hi,

    Try this.

    =SUMIF(Alot[[#All],[Column1]],AY2,Ulot[[#All],[Column1]])

    Thanks

  8. #8
    New Member
    Join Date
    Jan 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    anand_babu123:

    Thanks.

    I think I figured it out, bit I'll try it later. This bit helped: The SUMIF Function using Named Ranges

    So ! =SUMIF(A$2:A$1760,AY2,U$2:U$1760) becomes =SUMIF(Alot,AY2,Ulot)

    Thanks for your help.


    Quote Originally Posted by anand_babu123 View Post
    Hi,

    Try this.

    =SUMIF(Alot[[#All],[Column1]],AY2,Ulot[[#All],[Column1]])

    Thanks

  9. #9
    New Member
    Join Date
    Jan 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    Andrew:
    How can I format a worksheet version of this ? = Range("A" & Rows.Count).End(xlUp).

    I still need to sum some columns to the last used row from Z2; to lastrow -1
    Thanks


    Quote Originally Posted by Andrew Poulsom View Post
    First assign the row number of the last used cell in column A to a variable:

    Code:
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Then the formula string would be "=SUMIF(A$2:A$" & LastRow & ",AY2,U$2:U$" & LastRow & ")".

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using SumIF worksheet function to the end of the column

    That depends on where you want to put the formula.
    Microsoft MVP - Excel

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com