Sum in dynamic range

[khtanned]

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

 

[Tetra201]

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]

Hi Tetra201,

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

Sincerely
khtan

 

[Tetra201]

You are welcome.