I just found this site and I'm hoping someone here can help me!

Basically, I run a points rewards program for the company I'm with and built a complex formula to determine points earned for our customers. I'll give you a quick breakdown of how the points work before showing the formula.

Every customer is given a base quota. This is the amount of money they need to spend in order to be eligible to earn points.

Once they reach and exceed the base quota, their sales all the way back to January are then accounted for to earn points.

The customer earns 2 points for every 1 dollar they spend up to the base quota. Once they exceed this quota, they begin to earn 4 points for every dollar they spend thereafter.

The issue I'm having is the month in which they have exceeded this base quota. I had written it in plain text on how it should be laid out then converted it to a formula. This is what I came up with:

=IF(SUM($M2:T2)>$L2,(IF(SUM(SUM($M2:S2))>$L2,T2*4,(SUM((SUM($M2:T2)-$L2)*4)+

**(SUM($T2-(SUM(M2:T2)-$L2*2))))**,T2*2)))

Please note that columns M to T are customers monthly sales from January to August, column L is their base quota.

=IF(SUM(Jan:Jul)>Basequota,(IF(SUM(SUM(Jan:Jul))>Basequota,Aug*4,(SUM((SUM(Jan:Aug)-Basequota)*4)+

**(SUM(Aug-(SUM(Jan:Aug)-Basequota*2))))**,Aug*2)))

The Bold, Red portion of the formula is where I KNOW I'm having the error.

Basically, this portion needs to say: Total January to August sales minus the base quota times 4 (For the 4pts per dollar spent above base quota) PLUS August's Sales minus (Jan-Aug Sales minus the base quota), times two.

Any help you can provide would be fantastic!

Here is a sample of a customer's information to test if the formula is accurate.

Base quota: 150,000

January Sales: 11,177

February Sales: 20,433

March Sales: 12,875

April Sales: 22,655

May Sales: 32,664

June Sales: 17,451

July Sales: 18,397

August Sales: 14,647

This customer has YTD sales of 150,299. Their points total SHOULD equal 301,196. August's points should be 29,892

Thanks for your help!