Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 349
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 70
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 349
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF and VLOOKUP formula Iraj Excel Discussion (Misc queries) 9 March 18th 09 04:21 AM
Alternative formula to the vlookup formula? Victor Excel Worksheet Functions 2 May 12th 08 04:38 PM
convert vlookup formula to link formula AFA Excel Worksheet Functions 0 February 20th 08 05:24 AM
Excel 2002 VLOOKUP formula or other formula Serge Excel Discussion (Misc queries) 4 February 26th 07 04:56 PM
a possible vlookup formula....help changetires Excel Discussion (Misc queries) 0 June 23rd 06 02:05 AM


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"