sumproduct (link to another excel workbook)

**ronlau123** · 08-13-2011, 12:39 PM

Dear all,

Every week, I also receive the excel file (sourcefinal.xls), it is regarding account manager's sales summary.

I have another workbook called summaryfinal. xls. It includes sales summary of acccount manager. I know that I can use sumproduct formula to retrieve/get data from the sourcefinal.xls

In the sourcefinal excel, we have 4 types of product, od, fund, loan and mortgage. For loan and mortgage, it divided into number of product sold and total sales amount of the product. In the excel, we just need ytd, year to data data.

HOwever, I spend two hours, still can't fix it. Appreciate your help. Thanks

**ronlau123** · 08-16-2011, 12:07 AM

Dear all big brothers,

I need your help. I sure that sumproduct formula can help me. I can clarify the question and please let me know anything is wrong.

Appreciate for your reply.

**Haseeb Avarakkan** · 08-16-2011, 06:54 PM

Hi Ron,

See the attached.

**ronlau123** · 08-17-2011, 09:02 PM

Thanks for your reply.

May I ask is there any good methods to change sourcefinal.xls], if sometimes, the location of the sourcefinal.xls] is changed. It usually put in the share drive, sometimes, IT department will do integration/ moving file to another file.

Is it any good ways to change the link of sources file? using "edit link" function?

=SUMPRODUCT(--([sourcefinal.xls]sourceAB!$A$11:$A$30=$A11),--([sourcefinal.xls]sourceAB!$B$11:$B$30=B$10),[sourcefinal.xls]sourceAB!$G$11:$G$30)