I have a spreadsheet of user transactions and naturally some users will have one or more transactions. My objective is to add up the total(spent) of each user ( not necessary display it )and display the user whos spent the most and spent the least.
I cannot use a pivot table and if you can show me the formula with the simple table below that would be very helpful.
I have tried the max and averageIF functions, but I can't get the code to work. This is just a sample table but concept will apply.
| UserID | Amount |
|---|---|
| 1 | 122 |
| 2 | 400 |
| 3 | 122 |
| 1 | 300 |
| 4 | 400 |
| 5 | 100 |
| 2 | 50 |
| 1 | 600 |
| 3 | 400 |
| 2 | 320 |
So the end result will be User with Max spend = 1 and Min spend = 5
This works for me in Excel 2013. They are array formulas so you have to press ctrl+shift+enter to input them. This assumes column A is your ID and column B is your amount.
=INDEX($A$1:$A$10,MATCH(MAX(SUMIF($A$1:$A$10,A1:A10,$B$1:$B$10)),SUMIF($A$1:$A$10,A1:A10,$B$1:$B$10),0))
=INDEX($A$1:$A$10,MATCH(MIN(SUMIF($A$1:$A$10,A1:A10,$B$1:$B$10)),SUMIF($A$1:$A$10,A1:A10,$B$1:$B$10),0))