Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup and Sum If formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup and Sum If formula
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup and Sum If formula
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and VLOOKUP formula | Excel Discussion (Misc queries) | |||
Alternative formula to the vlookup formula? | Excel Worksheet Functions | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
a possible vlookup formula....help | Excel Discussion (Misc queries) |