Sum in dynamic range

khtanned

New Member
Joined
Oct 6, 2018
Messages
9
Greetings. May I seek advice on this matter.
In cell B4 of the worksheet example, I want to sum the Sales in column E based on criteria in B1 and B2.
In the example B1 is 2003 and B2 is $2000.
In B4 I wish to sum a dynamic range starting from year 2003 (B1) that is less than the value in B2.
The result in this case would be $1750 from range E5:E9.
If I change the values of the B1 and B2 to 2001 and $3000 respectively, I should get $2600 in cell B4
which is the sum of E3:E10.
Is there a way to do this using formula without VBA?

Thank you
Sincerely
khtan

Example1.jpg
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
If you don't mind having a (hidden) helper column (say, column X) then try the following:

1) Enter =SUM(E$1:E1) in cell X1 and drag-copy down to X12
2) Enter =LOOKUP(B2,X1:X12-LOOKUP(B1,D2:D12,X1:X11)) in cell B4
 

khtanned

New Member
Joined
Oct 6, 2018
Messages
9
Hi Tetra201,

That worked out just fine! I'm now trying to figure out how it works.
Thank you so much!!

Sincerely
khtan
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,729
Members
416,130
Latest member
galgozzi

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
Top