# Sum in dynamic range

#### khtanned

##### New Member
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

### 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
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
Hi Tetra201,

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

Sincerely
khtan

You are welcome.

Replies
2
Views
114
Replies
3
Views
69
Replies
8
Views
151
Replies
3
Views
384
Replies
3
Views
182

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.

### Which adblocker are you using?

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

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