Another approach:
Your
Sheet1 with the sample data:
In Column
I is the customer name, Column
M is the revenue earned for a single transaction, and Column
N is the account type.
Excel 2016 (Windows) 64 bit
| F | G | H | I | J | K | L | M | N |
---|
1 | Type | Date | Num | name | Memo | Account | Split | revenu | account_type |
2 | Invoice | 1-4-2019 | 263393 | Buchanan, Steven | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 10.000,00 | Income |
3 | Invoice | 1-3-2019 | 262826 | Suyama, Michael | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 2.500,00 | Cost of Goods |
4 | Invoice | 1-2-2019 | 261925 | Peacock, Margaret | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 20.000,00 | Income |
5 | Invoice | 1-2-2019 | 263180 | Leverling, Janet | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 5.000,00 | Income |
6 | Invoice | 1-2-2019 | 263017 | Peacock, Margaret | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 50.000,00 | Cost of Goods |
7 | Invoice | 1-2-2019 | 263017 | Leverling, Janet | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 5.000,00 | Income |
8 | Invoice | 1-2-2019 | 262963 | Buchanan, Steven | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 75.000,00 | Income |
9 | Invoice | 1-2-2019 | 263331 | Dodsworth, Anne | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 100.000,00 | Income |
10 | Invoice | 1-1-2019 | 269790 | Leverling, Janet | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 12.000,00 | Cost of Goods |
11 | Invoice | 1-4-2019 | 263367 | Peacock, Margaret | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 10.000,00 | Income |
12 | Invoice | 1-3-2019 | 262983 | Davolio, Nancy | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 2.500,00 | Cost of Goods |
13 | Invoice | 1-2-2019 | 267853 | Peacock, Margaret | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 20.000,00 | Income |
14 | Invoice | 1-2-2019 | 298456 | Peacock, Margaret | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 5.000,00 | Income |
15 | Invoice | 1-2-2019 | 263012 | Peacock, Margaret | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 50.000,00 | Cost of Goods |
16 | Invoice | 1-2-2019 | 263915 | Callahan, Laura | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 5.000,00 | Income |
17 | Invoice | 1-2-2019 | 267654 | Dodsworth, Anne | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 75.000,00 | Income |
18 | Invoice | 1-2-2019 | 268961 | Suyama, Michael | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 100.000,00 | Income |
19 | Invoice | 1-1-2019 | 259789 | Fuller, Andrew | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 12.000,00 | Cost of Goods |
20 | Invoice | 1-2-2019 | 263915 | Leverling, Janet | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 5.000,00 | Income |
21 | Invoice | 1-2-2019 | 267654 | Peacock, Margaret | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 75.000,00 | Income |
22 | Invoice | 1-2-2019 | 268961 | Callahan, Laura | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 100.000,00 | Income |
23 | Invoice | 1-1-2019 | 259789 | Buchanan, Steven | SALES | 40005 - CHTR SALES | 11000 - ACCOUNTS RECEIVABLE | € 12.000,00 | Cost of Goods |
Then, on
Sheet2 starting from
A5 (sorry it's not from A1 ;0))
In
A1, enter how many records you want to fetch. Enter that number manually. In this example I entered 6.
In
A2 Control+Shift+Enter; not just Enter:
=LARGE(SUMIFS(Sheet1!$M$2:$M$23;Sheet1!$I$2:$I$23;IF(FREQUENCY(IF(1-(Sheet1!$I$2:$I$23="");MATCH(Sheet1!$I$2:$I$23;Sheet1!$I$2:$I$23;0));ROW(Sheet1!$I$2:$I$23)-ROW(Sheet1!$I$2)+1);Sheet1!$I$2:$I$23);Sheet1!$N$2:$N$23;"Income");MIN(A1;SUM(IF(FREQUENCY(IF(1-(Sheet1!$I$2:$I$23="");MATCH(Sheet1!$I$2:$I$23;Sheet1!$I$2:$I$23;0));ROW(Sheet1!$I$2:$I$23)-ROW(Sheet1!$I$2)+1);1))))
In
A3 Control+Shift+Enter; not just Enter:
=IFERROR(SUM(IF(SUMIFS(Sheet1!$M$2:$M$23;Sheet1!$I$2:$I$23;IF(FREQUENCY(IF(1-(Sheet1!$I$2:$I$23="");MATCH(Sheet1!$I$2:$I$23;Sheet1!$I$2:$I$23;0));ROW(Sheet1!$I$2:$I$23)-ROW(Sheet1!$I$2)+1);Sheet1!$I$2:$I$23))>=A2;1));0)
In
A5 Control+Shift+Enter; not just Enter; and copy down:
=IF($B5="";"";INDEX(Sheet1!$I$2:$I$23;SMALL(IF(SUMIFS(Sheet1!$M$2:$M$23;Sheet1!$I$2:$I$23;IF(FREQUENCY(IF(1-(Sheet1!$I$2:$I$23="");MATCH(Sheet1!$I$2:$I$23;Sheet1!$I$2:$I$23;0));ROW(Sheet1!$I$2:$I$23)-ROW(Sheet1!$I$2)+1);Sheet1!$I$2:$I$23);Sheet1!$N$2:$N$23;"Income")=$B5;ROW(Sheet1!$I$2:$I$23)-ROW(Sheet1!$I$2)+1);COUNTIFS($B$5:B5;B5))))
In
B5 Control+Shift+Enter; not just Enter; and copy down:
=IF(ROWS($B$5:B5)>$A$3;"";LARGE(SUMIFS(Sheet1!$M$2:$M$23;Sheet1!$I$2:$I$23;IF(FREQUENCY(IF(1-(Sheet1!$I$2:$I$23="");MATCH(Sheet1!$I$2:$I$23;Sheet1!$I$2:$I$23;0));ROW(Sheet1!$I$2:$I$23)-ROW(Sheet1!$I$2)+1);Sheet1!$I$2:$I$23);Sheet1!$N$2:$N$23;"Income");ROWS($B$5:B5)))
Excel 2016 (Windows) 64 bit
| A | B |
---|
1 | 6 | |
2 | 15000 | |
3 | 6 | |
4 | individual | largest total |
5 | Dodsworth, Anne | € 175.000,00 |
6 | Peacock, Margaret | € 130.000,00 |
7 | Callahan, Laura | € 105.000,00 |
8 | Suyama, Michael | € 100.000,00 |
9 | Buchanan, Steven | € 85.000,00 |
10 | Leverling, Janet | € 15.000,00 |
11 | | |
12 | | |
13 | | |
14 | | |
15 | | |
16 | | |