Calculating Commission

Commissions are paid based upon the volume generated in the past 365 days.
For example: When volume generated is between $0 - $200,000 over the last 365 days the commission level is 3%; when volume generated over the past 365 days is between $200,000 -$500,000 the commission is 4%; between $500,000 - $750,000 is 5%

Columns are:

Date Sale Price Agent

And there is a lookup table with the dollar volumes and corresponding commission rates.

It's mostly incorporating the previous 365 days from each sale date into the formula that I can't figure. Any help would be appreciated.

you need a nested if statement


NOTE: substitute A2 in the formula for the reference to the volume of sales