Using last row in a VBA SUMIFS formula

Do you prefer VBA?

This sounds like something that can be easily done with formula.
Hard to tell without a representative sample of what you are working with and what you aim to accomplish.

If you are not familiar with how to upload a sample Excel file:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Thanks Dave,

I'm using VBA to add the formulas and then copy and paste as values to remove the formulas - this saves the spreadsheet re calculating each formula every time something changes (ie no formulas left in the spreadsheet)

I have five worksheets that each have a different 'last row' and currently contains between 104,456 and 149,116 rows each with new data being added daily.

Instead of using $A:$A, $B:$B etc in the formula I want to use

$A2:A & LRow - which would resemble $A2:$A104456

so lRow automatically changes to the last row, after new daily data is added, in the hope that it will speed up the calculations.

Unfortunately due to departmental IT restrictions I cannot attach a copy of the spreadsheet as the macros will be disabled.

Hope this makes a little more sense on what I'm trying to achieve.

Chnag the code from this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Cheers

Dean

You need to do it like below...

"=SUMIFS(AAA!$F2:$F" & lRow & ",AAA!$A2:$A" & lRow & ",Graphs!$G$1,AAA!$B2:$B" & lRow & ",'Graph data'!A" & I + 3 & ")"

You're welcome Dean! Glad it worked as desired.

Happy New Year!