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!

 

[kweaver]

Can you show a larger example of the sheet? This one is unreadable.

 

[kweaver]

Guessing about the structure, try this in A2 (assumed headings in row 1) and fill down:

=IFERROR(INDEX($I$2:$I$20,MATCH(AGGREGATE(14,6,($N$2:$N$20="Income")*($M$2:$M$20),ROW()-1),$M$2:$M$20,0),1),"")

except this doesn't consider TIES.

 

[kweaver]

This is rather ugly and pulls a fast one on the data (using the assumption that the revenue amounts are ALL INTEGERS). I believe it will handle the case(s) of duplicate revenues in the ranking.
It's also an array formula: CTRL+Shift+Enter

=IFERROR(INDEX($I$2:$I$20,MATCH(AGGREGATE(14,6,("Income"=$N$2:$N$20)*($M$2:$M$20+ROW($M$2:$M$20)/10000),ROWS(E$2:E2)),$M$2:$M$20+ROW($M$2:$M$20)/10000,0),1),"")

 

[conny2254]

Thank you for your replies kweaver!

Your formula returns the following starting in cell A2

[TABLE="width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CUSTOMER C[/TD]
[/TR]
[TR]
[TD]CUSTOMER C[/TD]
[/TR]
[TR]
[TD]CUSTOMER A[/TD]
[/TR]
[TR]
[TD]CUSTOMER A[/TD]
[/TR]
[TR]
[TD]CUSTOMER B[/TD]
[/TR]
[TR]
[TD]CUSTOMER B[/TD]
[/TR]
</tbody>[/TABLE]

I think the formula is looking at each row individually and ranking the customer with the highest single revenue row but I am trying to determine and rank the customer with the highest total revenue in the list and rank each customer with the highest total revenue in A2:A21. In the above list CUSTOMER A, CUSTOMER B, and CUSTOMER C should each only be listed once.

Is that possible?

Thank you!

 

[kweaver]

Are you sure you entered an array formula and filled that down? Check my sample data since I couldn't read yours.

Excel 2010
	A	B	C	D	E	F	G	H	I	M	N
1									Name	Revenue	Accnt Type
2	Customer M								Customer A	100	Income
3	Customer L								Customer B	200	whatever
4	Customer G								Customer C	3000	Income
5	Customer C								Customer D	400	Income
6	Customer F								Customer E	5000	who knows
7	Customer H								Customer F	2650	Income
8	Customer D								Customer G	5000	Income
9	Customer A								Customer H	2600	Income
10									Customer I	1900	whatever
11									Customer J	6000	something
12									Customer K	6200	whaelse
13									Customer L	5000	Income
14									Customer M	6000	Income
15									Customer N
16									Customer O
17									Customer P
18									Customer Q
19									Customer V
20									Customer W
Sheet10

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX($I$2:$I$20,MATCH(AGGREGATE(14,6,("Income"=$N$2:$N$20)*($M$2:$M$20+ROW($M$2:$M$20)/10000),ROWS(E$2:E2)),$M$2:$M$20+ROW($M$2:$M$20)/10000,0),1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[conny2254]

Hi kweaver,

I did enter as an array, and I think the difference is the same customer could be listed multiple times in Column I where as your data lists each customer once. Below is my data, i do not know how you posted an excel view of your data or I would do the same, thank you again!



[TABLE="width: 1140"]
<colgroup><col><col span="4"><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Num[/TD]
[TD]Name[/TD]
[TD]Memo[/TD]
[TD]Account[/TD]
[TD]Split[/TD]
[TD]Amount[/TD]
[TD]Account Type[/TD]
[/TR]
[TR]
[TD]CUSTOMER C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/04/2019[/TD]
[TD="align: right"]263393[/TD]
[TD]CUSTOMER A[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]10,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]CUSTOMER C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/03/2019[/TD]
[TD="align: right"]262826[/TD]
[TD]CUSTOMER A[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]2,500.00[/TD]
[TD]Cost of Goods[/TD]
[/TR]
[TR]
[TD]CUSTOMER A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]261925[/TD]
[TD]CUSTOMER A[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]20,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]CUSTOMER A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263180[/TD]
[TD]CUSTOMER B[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]5,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]CUSTOMER B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263017[/TD]
[TD]CUSTOMER B[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]50,000.00[/TD]
[TD]Cost of Goods[/TD]
[/TR]
[TR]
[TD]CUSTOMER B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263017[/TD]
[TD]CUSTOMER B[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]5,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]262963[/TD]
[TD]CUSTOMER C[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]75,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263331[/TD]
[TD]CUSTOMER C[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]100,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/01/2019[/TD]
[TD="align: right"]259790[/TD]
[TD]CUSTOMER C[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]12,000.00[/TD]
[TD]Cost of Goods[/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

If your A column shown above (to the left of Type) is CORRECT, then the array formula does work. If you want the customers listed as UNIQUE names, there are a few options.
1. you could filter the data based on the A column or
2. I added a helper column (see below) to give the unique customer names and then HIDE the A column.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Ranking	Ranking	Type	Date	Num	Name	Memo	Account	Split	Amount	Account Type
2	CUSTOMER C	CUSTOMER C	Invoice	1/4/2019	263393	CUSTOMER A	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	10,000.00	Income
3	CUSTOMER C	CUSTOMER A	Invoice	1/3/2019	262826	CUSTOMER A	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	2,500.00	Cost of Goods
4	CUSTOMER A	CUSTOMER B	Invoice	1/2/2019	261925	CUSTOMER A	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	20,000.00	Income
5	CUSTOMER A		Invoice	1/2/2019	263180	CUSTOMER B	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	5,000.00	Income
6	CUSTOMER B		Invoice	1/2/2019	263017	CUSTOMER B	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	50,000.00	Cost of Goods
7	CUSTOMER B		Invoice	1/2/2019	263017	CUSTOMER B	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	5,000.00	Income
8			Invoice	1/2/2019	262963	CUSTOMER C	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	75,000.00	Income
9			Invoice	1/2/2019	263331	CUSTOMER C	SALES	40005 - CHTR SALES	11000 - ACCOUNTS RECEIVABLE	100,000.00	Income
10			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
A2	=IFERROR(INDEX($F$2:$F$10,MATCH(AGGREGATE(14,6,($K$2:$K$10="Income")*($J$2:$J$10),ROW()-1),$J$2:$J$10,0),1),"")
B2	{=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$10),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[conny2254]

Thank you for looking at this. I think the formula is working as its supposed to. It is only showing the largest customer single sale. I slightly changed the below data and gave CUSTOMER B a sale of $105,000. The formula shows CUSTOMER B as the first customer with the most sales but CUSTOMER C has the most total sales with 175,000 in total sales. Is it possible to list the customer with the most total sales? Thank you again.



[TABLE="width: 1205"]
<colgroup><col><col><col span="3"><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Num[/TD]
[TD]Name[/TD]
[TD]Memo[/TD]
[TD]Account[/TD]
[TD]Split[/TD]
[TD]Amount[/TD]
[TD]Account Type[/TD]
[/TR]
[TR]
[TD]CUSTOMER B[/TD]
[TD]CUSTOMER B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/04/2019[/TD]
[TD="align: right"]263393[/TD]
[TD]CUSTOMER A[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]10,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]CUSTOMER C[/TD]
[TD]CUSTOMER C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/03/2019[/TD]
[TD="align: right"]262826[/TD]
[TD]CUSTOMER A[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]2,500.00[/TD]
[TD]Cost of Goods[/TD]
[/TR]
[TR]
[TD]CUSTOMER C[/TD]
[TD]CUSTOMER A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]261925[/TD]
[TD]CUSTOMER A[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]20,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]CUSTOMER A[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263180[/TD]
[TD]CUSTOMER B[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]105,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]CUSTOMER A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263017[/TD]
[TD]CUSTOMER B[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]50,000.00[/TD]
[TD]Cost of Goods[/TD]
[/TR]
[TR]
[TD]CUSTOMER B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263017[/TD]
[TD]CUSTOMER B[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]5,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]262963[/TD]
[TD]CUSTOMER C[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]75,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/02/2019[/TD]
[TD="align: right"]263331[/TD]
[TD]CUSTOMER C[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]100,000.00[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]01/01/2019[/TD]
[TD="align: right"]259790[/TD]
[TD]CUSTOMER C[/TD]
[TD]SALES[/TD]
[TD]40005 · CHTR SALES[/TD]
[TD]11000 · ACCOUNTS RECEIVABLE[/TD]
[TD]12,000.00[/TD]
[TD]Cost of Goods[/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

AH HA...you need each customer's total to be used not a line by line. Let me think about that.