Excel Formula to sum customers List with criteria and show return the customer with highest Revenue

[conny2254]

Ok, I have been unable to solve this formula.


I have a list of financial transactions on a sheet named Income Stmnt Transaction. In Column I is the customer name, Column M is the revenue earned for a single transaction, and Column N is the account type.


the customer who earned the most revenue is unknown and needs to be found. The formula should only total revenues if Column N is equal to Income.


I want to make a list in a new sheet from A1 to A20 showing the customer which earned the most revenue in A1 the second most in A2 and so on all the way to A20. Below is an example image of the Income Stmnt Transaction sheet.

In the above abbreviated example A1 would be Customer C, A2 would be Customer A, and A3 would be Customer B.

Can anyone help with this formula?

Thank you in advance!

 

[strooman]

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

Sheet: Sheet1

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​

Sheet: Sheet2

 

[kweaver]

I like helper columns.
You can always hide columns A and B.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Unique Cusstomers	Totals	Ranking	Type	Date	Num	Name	Memo	Account	Split	Amount	Account Type
2	CUSTOMER A	30000	CUSTOMER C	Invoice	1/4/2019	263393	CUSTOMER A	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	10,000.00	Income
3	CUSTOMER B	10000	CUSTOMER A	Invoice	1/3/2019	262826	CUSTOMER A	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	2,500.00	Cost of Goods
4	CUSTOMER C	175000	CUSTOMER B	Invoice	1/2/2019	261925	CUSTOMER A	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	20,000.00	Income
5		0		Invoice	1/2/2019	263180	CUSTOMER B	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	5,000.00	Income
6		0		Invoice	1/2/2019	263017	CUSTOMER B	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	50,000.00	Cost of Goods
7		0		Invoice	1/2/2019	263017	CUSTOMER B	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	5,000.00	Income
8		0		Invoice	1/2/2019	262963	CUSTOMER C	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	75,000.00	Income
9		0		Invoice	1/2/2019	263331	CUSTOMER C	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	100,000.00	Income
10		0		Invoice	1/1/2019	259790	CUSTOMER C	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	12,000.00	Cost of Goods
Sheet10

Cell Formulas
Range	Formula
B2	=AGGREGATE(14,6,SUMPRODUCT(($K$2:$K$10)*("Income"=$L$2:$L$10)*(A2=$G$2:$G$10)),1)
C2	=INDEX($A$2:$A$10,MATCH(LARGE($B$2:$B$10,ROW()-1),$B$2:$B$10,0),1)
A2	{=IFERROR(INDEX($G$2:$G$10,MATCH(0,COUNTIF($A$1:A1,$G$2:$G$10),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[conny2254]

Hi Strooman,

Thank you for looking at this. Can you tell me what A2 and A3 are calculating? Also, does A5 them shows the customer with the highest total sales, correct? And B5 shoes the amount of the total sales? Is this correct?

Thank you!

 

[strooman]

Hi Strooman,

Thank you for looking at this. Can you tell me what A2 and A3 are calculating? Also, does A5 them shows the customer with the highest total sales, correct? And B5 shoes the amount of the total sales? Is this correct?

Thank you!

A2 calculates the conditional sum of the last occurence, in this case the number 6 (N=6).
A3 Looks for a tie. For example 2 customers are tied with the same total amount. This cell re-calculates our Top N value when there is a tie.

Yes, A5 and B5 show the amount of total sales correctly when the criteria is met, the account type must be "Income", like you pointed out.

The outcome in A2 and A3 is needed to get the correct order in A5 and B5. These values have to correspond with each other to get the proper result.

 

[conny2254]

Hi Strooman/kweaver,

I think both of these options work! So thank you very much! I was attempting to accomplish this without a helper Column. The solutions provided will meet my needs but I am curious, is this calculation possible without the helper column?

 

[kweaver]

I'm not sure and don't have time today to check on that.
I personally don't mind helper columns and don't hesitate to hide them if I need to ignore them for my work.

Glad we both could offer alternatives. Good luck with the project.

 

[strooman]

The solutions provided will meet my needs but I am curious, is this calculation possible without the helper column?

Of course. That is just a lay-out problem. So this is what you want (if I'm correct):
- Data is on Sheet 1 Columns F to M.
- On Sheet 2 you only want the names of the customers in Column A en descending order.

Then insert a Sheet 3 and Cut (Ctrl+C) the calculations that were previous on Sheet 2 and Paste (Ctrl+V) them on Sheet 3. Pay attention to Cut the calculations to keep the formulas intact. Then you will end up with the following (lay-out) results:

	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

Sheet: Sheet1

	A​
1​	Dodsworth, Anne
2​	Peacock, Margaret
3​	Callahan, Laura
4​	Suyama, Michael
5​	Buchanan, Steven
6​	Leverling, Janet
7​
8​
9​
10​
11​
12​

Sheet: Sheet2

	A​	B​	C​
1​	6​		largest total
2​	15000​		€ 175.000,00​
3​	6​		€ 130.000,00​
4​			€ 105.000,00​
5​			€ 100.000,00​
6​			€ 85.000,00​
7​			€ 15.000,00​
8​
9​
10​
11​
12​
13​

Sheet: Sheet3

 

[conny2254]

This all appears to work! Thank you all very much for your assistance.