VLookup and Sum If formula

[Peter]

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 D24+ 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

 

[John Moore]

Use SUMPRODUCT, this should capture what you need ok.

 

[Peter]

Hi John - thank you for your response

I am not sure how the sumproduct will help with regards to picking up the
missing job no from sheet 2 and giving it 60 as terms - so

=SUMPRODUCT(--((C16:C21=D2833)),IF(E28:E33+B16>C3,D1621),IF(E28:E33+B16<=D3,D1621))

Still gives me 1000 short. - pehaps just me misunderstanding the full
potential of SUMPRODUCT as not really used it before.

 

[Gary McCarthy]

Perhaps this will work -

Assuming Job No on Sheet 2 is in column A and Terms are in Column B and
range is through row 64 -

On Sheet1, Job No is in A2

In cell for Terms, here B2, -


=if(iserror(VLOOKUP($A2,Sheet2!$A$1:$B$64,2,FALSE)),60,VLOOKUP($A2,Sheet2!$A$1:$B$64,2,FALSE))

This will return the terms for the Job No if on Sheet2 but return 60 if not.

hth