SUM All the Action from Users

[mastercands]

Hi All,

I have a new question that till now I did not find an easy solution:

1) Users

I got a file (file3.xls) with users of my company:
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]users[/TD]
[/TR]
[TR]
[TD]123[/TD]
[/TR]
[TR]
[TD]234[/TD]
[/TR]
[TR]
[TD]345[/TD]
[/TR]
[TR]
[TD]456[/TD]
[/TR]
[TR]
[TD]567[/TD]
[/TR]
[TR]
[TD]678[/TD]
[/TR]
[TR]
[TD]789[/TD]
[/TR]
</tbody>[/TABLE]

2) Other file (file1.xls) with Users and Accounts. Each users can have different records with different accounts. This means the user had a contact with the account.
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt; text-align: center;"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 128, colspan: 2, align: center"]file1[/TD]
[/TR]
[TR]
[TD]users[/TD]
[TD]accounts number[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]345[/TD]
[TD]3456[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]4567[/TD]
[/TR]
[TR]
[TD]567[/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]678[/TD]
[TD]6789[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]7890[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]3456[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]4567[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]567[/TD]
[TD]6789[/TD]
[/TR]
[TR]
[TD]678[/TD]
[TD]7890[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]3456[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]4567[/TD]
[/TR]
</tbody>[/TABLE]

3) The fie (file2.xls) has Account Transaction for the specific account number.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt; text-align: center;"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 128, colspan: 2, align: center"]file2[/TD]
[/TR]
[TR]
[TD="align: center"]accounts number[/TD]
[TD="align: center"]cash[/TD]
[/TR]
[TR]
[TD="align: center"]2345[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4567[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6789[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7890[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4567[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6789[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7890[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4567[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


My point:
1) I want to know for each user what was to total of money they transacted.

Eg: user 123 had contacted the account 1234 (2 times) and 2345 (4times). So the total for user 123 should be the value of the transactions of 1234 + transactions of the account 2345.

Note that the cash I put 1. But in real world is different values.

If you require more details, please elaborate.

 

[Aladin Akyurek]

Are you talking about 3 worksheets or workbooks?

 

[mastercands]

3 Excel Files (I think is workbook).
File1.xls = Users and Accounts
File2.xls = Accounts and money
File3.xls = user list

 

[Aladin Akyurek]

3 Excel Files (I think is workbook).
File1.xls = Users and Accounts
File2.xls = Accounts and money
File3.xls = user list

File2, Sheet1, B2, control+shift+enter, not just enter, and copy down:

=SUM(IF(ISNUMBER(MATCH([file2.xlsx]Sheet1!$A$2:$A$19,
  IF([file1.xlsx]Sheet1!$A$2:$A$19=$A2,[file1.xlsx]Sheet1!$B$2:$B$19),0)),
  [file2.xlsx]Sheet1!$B$2:$B$19))