Using SumIF worksheet function to the end of the column

[Imagoodwon]

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.

 

[Imagoodwon]

I was trying to create a User defined function based on your code to call it from a cell on a worksheet.

That depends on where you want to put the formula.

 

[Andrew Poulsom]

Yes, but which cell? If it's at the bottom of the list, it may be included in the SUMIF, creating a circular reference.

 

[Imagoodwon]

I have a Dynamic Named Range called Total_Expenses

=OFFSET(Vendors!$D$2,0,0,COUNTA(Vendors!$D:$D),1)


In Column E2 I want to have a Total of that range minus the last used value,
because the worksheet was set up with SUM hard coded to add up those values in Column D.
If I use =SUM(Total_Expenses) it would include the whole selection, including the total already added in.


What I need is the equivalent of End(xlUp).Offset(-1, 0) that goes on a worksheet.


The SUM function goes in E2
The SUMIF function goes in Y2

Thanks

Yes, but which cell? If it's at the bottom of the list, it may be included in the SUMIF, creating a circular reference.

 

[Andrew Poulsom]

Can't you just divide the SUM by 2? Including the total line shouldn't matter to SUMIF.

 

[Imagoodwon]

Yup! THANKS !

Didn't think of that.
But for future reference, is there a formula that can go on the worksheet which functions like Lastrow .End(xlUp)

I know you can do this using VBA in a module, but I'm thinking of something with the same functionality , and used directly on a worksheet.

Can't you just divide the SUM by 2? Including the total line shouldn't matter to SUMIF.

 

[Andrew Poulsom]

To return a reference to the last numeric cell in column A:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

To use that reference to sum from A2 to that cell:

=SUM(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

[Imagoodwon]

Thanks Andrew:
Your assistance is greatly appreciated!

& A good link on the subject is here Referencing the Last Cell in a Column (Microsoft Excel)