Use SUMPRODUCT, this should capture what you need ok.

"Peter" wrote:

Hi all

Is there a way of using Vlookup and Sum(if( to show where if Job No in Sheet
2 is not in Sheet 1 then treat terms as 60.

Current formula that I am using does not capture amount that has the job no
missing.

=SUM(IF(Sheet 1 B2:B4= Sheet 2 a2:a4,IF( Sheet 2 D2:D4+ Sheet 1 A2<=Sheet 3,
Sheet 1 C2:C4)))


Sheet 1 Sheet 2

Invoice Date Job No £ Job No Terms
30/6/09 1222 1000 1222 30
30/6/09 1223 1000 1223 30
30/6/09 1880 1000

Sheet 3

Pay By Pay By
31/7 31/8 30/9 - Formula in sheet 3 col b uses date range
greater than
2000 - 31/7 and less than or = to 31/8

Sheet 3 Should show

31/7 31/8 30/9
2000 1000 -

Hope this makes sence.

Pete