formulas - Cross referencing Excel worksheets -

i'm working 3 worksheets.

projects consists of following:

project    clientcode   code      ------     ----------   ----      project1   abc          123       project2   abc          456       project3   def          789       

invoices consists of:

projectcode   amount -----------   ----- 123           $100 789           $200 123           $50 

and clients consists of:

code    total ----    ----- abc     [$150] def     [$200] 

i'm trying create formula populate "total" field on client sheet determining invoices belong project belong client. feel combination of sumif , lookup, i'm stumped.

edit: revised above format discussed below (swapped projects column b , c)

using vlookup , sumif in single cell without helper column possible, need interchange positions of columns clientcode , code in projects work.

interchange column positions mentioned above (so clientcode before code), use:

=sumif(invoices!a:a, vlookup(clients!a2, projects!b:c, 2, 0), invoices!b:b) 

i'm assuming row 1 of each worksheet has column headers. a2 here refers abc.

vlookup first looks code of clientcode , sumif sums amounts of matched code in invoices worksheet.

edit: below should work better since vlookup finds first match, doesn't work here.

=sum(sumif(invoices!a:a,if(clients!a1=projects!c:c,projects!b:b),invoices!f:f)) 

note have use ctrl+shift+enter use formula. after did abc, can drag formula b. note formula can take time evaluate, , such, might better if change ranges appropriate range. example, if invoices has 100 rows, change invoices!a:a, invoices!b:b invoices!a2:a100, invoices!b2:b100, same goes other ranges in formula.