Subtotal and Sumif with variable range

camandab

Board Regular
Joined
Feb 10, 2010
Messages
79
Hello,

I am stumped on an issue. I'm trying to build in a formula into a macro I have. There are a few worksheets that have 2 sections of data. The formula resides in between these sections. For example, section 1 of data will begin in row 2 and go to row 1000, section 2 will begin in row 1009 and go to row 2081, and the formula will be in row 1005. So the formula in row 1005 should sum or subtotal the data in section 1 (say L2:L1000) and the data in section 2 (say L1009:L2081). I'll also need to create another formula in row 1007 that will contain a sumif formula

Is there a way this can be done so I can have variable ranges between worksheets? Any help will be much appreciated!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sum column L if the values in column F are equal to 0 or greater than 900. Thanks in advance for your help!
 
Upvote 0
Code:
Cells(1, 12).End(xlDown).Offset(2, 0).Value = WorksheetFunction.Sum(Range(Cells(1, 12), Cells(1, 12).End(xlDown)))

This is just an example of vba to sum the variable length data in col L (section 1).

Is the data in col F grouped together and is it the total or the individual numbers that must exceed 900?
 
Upvote 0

Forum statistics

Threads
1,224,220
Messages
6,177,208
Members
452,764
Latest member
Mark1963

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