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:

users
123
234
345
456
567
678
789

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

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.

file1
users	accounts number
123	1234
234	2345
345	3456
456	4567
567	5678
678	6789
789	7890
123	1234
123	2345
234	3456
234	4567
456	5678
567	6789
678	7890
789	1234
123	2345
789	3456
789	4567

<colgroup><col width="64" span="2" style="width: 48pt; text-align: center;"> </colgroup><tbody>
</tbody>

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

file2
accounts number	cash
2345	1
3456	1
4567	1
5678	1
6789	1
7890	1
1234	1
2345	1
3456	1
4567	1
5678	1
6789	1
7890	1
1234	1
2345	1
2345	1
3456	1
4567	1

<colgroup><col width="64" span="2" style="width: 48pt; text-align: center;"> </colgroup><tbody>
</tbody>

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))