Using SumIF worksheet function to the end of the column

Imagoodwon

New Member
Joined
Jan 14, 2010
Messages
17
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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 & ")".
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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

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.
 
Upvote 0
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
</pre>

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 & ")".
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top