VLookup and Sum If formula
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
"Peter" wrote:
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=D28:D33)),IF(E28:E33+B16C3,D16:D21),IF( E28:E33+B16<=D3,D16:D21))
Still gives me 1000 short. - pehaps just me misunderstanding the full
potential of SUMPRODUCT as not really used it before.
"John Moore" wrote:
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
|