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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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,128,025
Messages
5,628,199
Members
416,300
Latest member
Kxs00301

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