Thanks:  0
Likes:  0

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

1. ## 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. ## 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 & ")".

3. ## 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. ## Re: Using SumIF worksheet function to the end of the column

Originally Posted by Andrew Poulsom
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. ## 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

Originally Posted by anand_babu123
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. ## Re: Using SumIF worksheet function to the end of the column

Hi,

Try this..

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

7. ## Re: Using SumIF worksheet function to the end of the column

Hi,

Try this.

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

Thanks

8. ## 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)

Originally Posted by anand_babu123
Hi,

Try this.

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

Thanks

9. ## 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

Originally Posted by Andrew Poulsom
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. ## Re: Using SumIF worksheet function to the end of the column

That depends on where you want to put the formula.

## User Tag List

#### Posting Permissions

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