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

C

conny2254

Board Regular
Joined
Jun 9, 2009
Messages
248
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.

2019-09-07.jpg



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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Can you show a larger example of the sheet? This one is unreadable.
 
Upvote 0
Guessing about the structure, try this in A2 (assumed headings in row 1) and fill down:

Code: 
=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.
 
Last edited:
Upvote 0
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

Code: 
=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),"")
 
Upvote 0
Thank you for your replies kweaver!

Your formula returns the following starting in cell A2

CUSTOMER C
CUSTOMER C
CUSTOMER A
CUSTOMER A
CUSTOMER B
CUSTOMER B

<colgroup><col></colgroup><tbody>
</tbody>

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!
 
Upvote 0
Are you sure you entered an array formula and filled that down? Check my sample data since I couldn't read yours.


Excel 2010
ABCDEFGHIMN
1NameRevenueAccnt Type
2Customer MCustomer A100Income
3Customer LCustomer B200whatever
4Customer GCustomer C3000Income
5Customer CCustomer D400Income
6Customer FCustomer E5000who knows
7Customer HCustomer F2650Income
8Customer DCustomer G5000Income
9Customer ACustomer H2600Income
10Customer I1900whatever
11Customer J6000something
12Customer K6200whaelse
13Customer L5000Income
14Customer M6000Income
15Customer N
16Customer O
17Customer P
18Customer Q
19Customer V
20Customer W
Sheet10
Cell Formulas
RangeFormula
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.
 
Upvote 0
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!



TypeDateNumNameMemoAccountSplitAmountAccount Type
CUSTOMER CInvoice01/04/2019263393CUSTOMER ASALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE10,000.00Income
CUSTOMER CInvoice01/03/2019262826CUSTOMER ASALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE2,500.00Cost of Goods
CUSTOMER AInvoice01/02/2019261925CUSTOMER ASALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE20,000.00Income
CUSTOMER AInvoice01/02/2019263180CUSTOMER BSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE5,000.00Income
CUSTOMER BInvoice01/02/2019263017CUSTOMER BSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE50,000.00Cost of Goods
CUSTOMER BInvoice01/02/2019263017CUSTOMER BSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE5,000.00Income
Invoice01/02/2019262963CUSTOMER CSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE75,000.00Income
Invoice01/02/2019263331CUSTOMER CSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE100,000.00Income
Invoice01/01/2019259790CUSTOMER CSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE12,000.00Cost of Goods

<colgroup><col><col span="4"><col span="5"><col><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
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
ABCDEFGHIJK
1RankingRankingTypeDateNumNameMemoAccountSplitAmountAccount Type
2CUSTOMER CCUSTOMER CInvoice1/4/2019263393CUSTOMER ASALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE10,000.00Income
3CUSTOMER CCUSTOMER AInvoice1/3/2019262826CUSTOMER ASALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE2,500.00Cost of Goods
4CUSTOMER ACUSTOMER BInvoice1/2/2019261925CUSTOMER ASALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE20,000.00Income
5CUSTOMER AInvoice1/2/2019263180CUSTOMER BSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE5,000.00Income
6CUSTOMER BInvoice1/2/2019263017CUSTOMER BSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE50,000.00Cost of Goods
7CUSTOMER BInvoice1/2/2019263017CUSTOMER BSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE5,000.00Income
8Invoice1/2/2019262963CUSTOMER CSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE75,000.00Income
9Invoice1/2/2019263331CUSTOMER CSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE100,000.00Income
10Invoice1/1/2019259790CUSTOMER CSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE12,000.00Cost of Goods
Sheet10
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.



TypeDateNumNameMemoAccountSplitAmountAccount Type
CUSTOMER BCUSTOMER BInvoice01/04/2019263393CUSTOMER ASALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE10,000.00Income
CUSTOMER CCUSTOMER CInvoice01/03/2019262826CUSTOMER ASALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE2,500.00Cost of Goods
CUSTOMER CCUSTOMER AInvoice01/02/2019261925CUSTOMER ASALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE20,000.00Income
CUSTOMER A0Invoice01/02/2019263180CUSTOMER BSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE105,000.00Income
CUSTOMER AInvoice01/02/2019263017CUSTOMER BSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE50,000.00Cost of Goods
CUSTOMER BInvoice01/02/2019263017CUSTOMER BSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE5,000.00Income
Invoice01/02/2019262963CUSTOMER CSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE75,000.00Income
Invoice01/02/2019263331CUSTOMER CSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE100,000.00Income
Invoice01/01/2019259790CUSTOMER CSALES40005 · CHTR SALES11000 · ACCOUNTS RECEIVABLE12,000.00Cost of Goods

<colgroup><col><col><col span="3"><col span="5"><col><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
AH HA...you need each customer's total to be used not a line by line. Let me think about that.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Knockoutpie
  • Solved
Excel formula to count unique cells in column Q with multiple criteria
Replies
7
Views
914
Fluff
Fluff
R
  • Question
Excel Formula for showing a picture.
Replies
1
Views
268
Anfinsen
A
J
  • Locked
  • Question
Excel Formula
Replies
1
Views
228
Joe4
Joe4
E
  • Question
Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.
Replies
4
Views
97
Event2020
E
etaf
  • Solved
SORT - an Extract List based on criteria - BUT 2019 Version ONLY - NOT 365
Replies
9
Views
470
Georgiboy
Georgiboy

Forum statistics

Threads
1,214,523
Messages
6,120,034
Members
448,940
Latest member
mdusw

Share this page

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