SUM All the Action from Users

mastercands

New Member
Joined
Apr 1, 2014
Messages
3
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
3 Excel Files (I think is workbook).
File1.xls = Users and Accounts
File2.xls = Accounts and money
File3.xls = user list
 
Upvote 0
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:
Rich (BB code):
=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))
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top