formula for Sum Total of two different workbooks in excel

[Kajal]

We have two different workbooks (Master & Slave) both the workbooks have one unique column i.e ID. Slave workbook has duplicates ID along with Amount figure. We want to get the sum total of those duplicates ID's amount and single entry amount should also be reflected on our Master workbook amount column.
eg

Slave Workbook

ID Amount
766 800
566 900
766 1000
675 200
566 100

Master Workbook

ID Amount
766
566
675

 

[sgmpatnaik]

@Kajal

Hi

Welcome to Forum, Glad you are here

What you want to achieve your

as per my understanding you want to sum the amount against IDS which is mention Master Book from Slave Workbook where you enter the data daily, if is it correct

then please follow the below instruction

suppose your data in the Column A & B of Slave Workbook and Single ID IN Master Workbook in the A

1. Open Both Workbooks

2. In Master Workbook write the below Formula in Column B1 as

3. =SUMIFS([Slave Workbook]Sheet1!$B$1:$B$5,[Slave Workbook]Sheet1!$A$1:$A$5,$A1)

hope it will solve your problem other wise please try to upload a sample work book


Thanks

SP

 

[Kajal]

Thanks SP for helping, but i am facing problem, as in when i enter the formula it shows "That name is not valid" and highlights Slave as i have shown in green color font.

=SUMIFS([Slave Workbook]Sheet1!$B$1:$B$5,[Slave Workbook]Sheet1!$A$1:$A$5,$A1)

 

[sgmpatnaik]

@Kajal

Hi

as per your formula in the first array you are not mention any space in the Workbook Name and in the second array you mention the workbook name with space

=SUMIFS([SlaveWorkbook]Sheet1!$B$1:$B$5,[Slave Workbook]Sheet1!$A$1:$A$5,$A1)

so please follow the below given instruction

1. Open Both Workbooks

2. write the formula in B1 of the Master Workbook as

3. =SUMIFS(

4. Select the range of the Slave Workbook of Column B for Example amount is stored in the Column B if any headings in the Column B1 then select from B2:B10 (range adjust as you like)

5. ,

6. Select the Range of the Slave Workbook of Column A range from A2:A10,$a1

7. Finally Bracket Close ) and enter

Hope it is clear your problem other wise please try to upload a sample workbook

Thanks

Patnaik

 

[Aladin Akyurek]

What are the real names of the two workbooks?