SUMPRODUCT help

[Gaurav]

Column A - name of the person. for example John Smith.
Column B - Primary ID JSMITH
Column C - Secondry ID JSMITH2

Column D needs to look for Primary and Secondry IDs in another workbook
column A and return the sum of the corresponding values in column D.

so other workbook

A1 - JSMITH.
A5 - JSMITH2
D1 - 15
D5 - 25

so i need the formula to return 40.

thanks in advance for any help.

 

[Gaurav]

well...SUMIF worked.

thanks for going through the post though.

 

[redbird]

Formula in cell D1 on first sheet:
=SUM((([SecondWorkbookName]SheetName!$A$1:$A$200=B1)+([SecondWorkbookName]SheetName!$A$1:$A$200=C1))*[SecondWorkbookName]SheetName!$D$1:$D$200)

Array enter (Shift-Ctrl-Enter)