Calculating points based on Date, Document Number and Total Sold

**david1987** · 12-20-2012, 11:20 AM

Hi,

I have five fields - item, document number, date of sale, total sold, points total.

I want to calculate the points total.

Each item has a points total associated with it. However, if the product was purchased before 14/11/2012 it is given a single points figure and if it is purchased after it is given a double points figure. The single points and double points are named ranges. I want to multiply the total sold by the points for that item, depending on whether it is single or double - based on when it was purchased.

I only want the calculation to be performed for products that have a document number beginning with 2. If it begins with a 3 then I don't want a calculation performed.

Any help would be appreciated, attached is a sample of the spreadsheet.

Thanks

David

**benishiryo** · 12-20-2012, 11:29 AM

hi David. assuming there's only documents starting with 2 & 3, try:
=IF(LEFT(B2,1)="3","",D2*(IF(C2+0<DATE(2012,11,14),VLOOKUP(A2,SinglePoints,2,0),VLOOKUP(A2,DoublePoints,2,0))))

*Edited after seeing Pete's formula & realised Column C is not an actual date

**Pete_UK** · 12-20-2012, 11:35 AM

Put this in E2:

=IF(LEFT(B2)<>"2",0,IF(--C2<--"14/11/2012",VLOOKUP(A2,SinglePoints,2,0),VLOOKUP(A2,DoublePoints,2,0))*D2)

then copy down.

Hope this helps.

Pete