Sum of all values found in vlookup

[MVI MIS]

Hello,

I have a pivot table and an array. The pivot table has an Invoice Num (eg. 22885) and a product cost. The pivot table data is pulled from an external source (SQLDB).

The array contains an Invoice Num (eg. 22885) and any freight charges that are incurred against that number. Sometimes, more than one freight charge is incurred and it becomes a 2nd line (having the Invoice Num in the proper column)

I'd like to create a field that does a vlookup of the Invoice Num on the Pivot table and return the sum of all freight to that invoice number.

Example: Pivot table has 22885 as the invoice num. The array has 2 invoice num fields that match, 200, and 45.50. I'd like the field to say 245.50

Here is the current formula I'm using, but it doesn't seem to be catching ALL the values, just the first:

=IFERROR(VLOOKUP([I]INVOICENUM[/I],'[I]SHEETWITHFREIGHTARRAY[/I]'!P:R,3,0),0)

I tried adding SUM to the formula, with no avail:

=IFERROR(SUM(VLOOKUP([I]INVOICENUM[/I],'[I]SHEETWITHFREIGHTARRAY[/I]'!P:R,3,0)),0)

Any ideas on how to accomplish this?

 

[Misca]

Try using SUMIF(S) or SUMPRODUCT instead of VLOOKUP.

 

[RoryA]

Welcome to the forum.

=SUMIF('SHEETWITHFREIGHTARRAY'!P:P,INVOICENUM,'SHEETWITHFREIGHTARRAY'!R:R)

should do the trick.

 

[MVI MIS]

Heroes.... you guys are heroes.

Thanks for your help.