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!
 
Sort by date Sort by votes
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#0070C0]Type[/td][td=bgcolor:#0070C0]Date[/td][td=bgcolor:#0070C0]Num[/td][td=bgcolor:#0070C0]name[/td][td=bgcolor:#0070C0]Memo[/td][td=bgcolor:#0070C0]Account[/td][td=bgcolor:#0070C0]Split[/td][td=bgcolor:#0070C0]revenu[/td][td=bgcolor:#0070C0]account_type[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Invoice[/td][td]
1-4-2019​
[/td][td]
263393​
[/td][td]Buchanan, Steven[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 10.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Invoice[/td][td]
1-3-2019​
[/td][td]
262826​
[/td][td]Suyama, Michael[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
261925​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 20.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263180​
[/td][td]Leverling, Janet[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263017​
[/td][td]Peacock, Margaret[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263017​
[/td][td]Leverling, Janet[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
262963​
[/td][td]Buchanan, Steven[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 75.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263331​
[/td][td]Dodsworth, Anne[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 100.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Invoice[/td][td]
1-1-2019​
[/td][td]
269790​
[/td][td]Leverling, Janet[/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]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Invoice[/td][td]
1-4-2019​
[/td][td]
263367​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 10.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]Invoice[/td][td]
1-3-2019​
[/td][td]
262983​
[/td][td]Davolio, Nancy[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
267853​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 20.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
298456​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263012​
[/td][td]Peacock, Margaret[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263915​
[/td][td]Callahan, Laura[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
267654​
[/td][td]Dodsworth, Anne[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 75.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
268961​
[/td][td]Suyama, Michael[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 100.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]Invoice[/td][td]
1-1-2019​
[/td][td]
259789​
[/td][td]Fuller, Andrew[/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]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263915​
[/td][td]Leverling, Janet[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
267654​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 75.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
268961​
[/td][td]Callahan, Laura[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 100.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]Invoice[/td][td]
1-1-2019​
[/td][td]
259789​
[/td][td]Buchanan, Steven[/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]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#FFFF00]
6​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#D6EDBD]
15000​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#D6EDBD]
6​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#0070C0]individual[/td][td=bgcolor:#0070C0]largest total[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#D6EDBD]Dodsworth, Anne[/td][td=bgcolor:#D6EDBD]
€ 175.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#D6EDBD]Peacock, Margaret[/td][td=bgcolor:#D6EDBD]
€ 130.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#D6EDBD]Callahan, Laura[/td][td=bgcolor:#D6EDBD]
€ 105.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#D6EDBD]Suyama, Michael[/td][td=bgcolor:#D6EDBD]
€ 100.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#D6EDBD]Buchanan, Steven[/td][td=bgcolor:#D6EDBD]
€ 85.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#D6EDBD]Leverling, Janet[/td][td=bgcolor:#D6EDBD]
€ 15.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td=bgcolor:#D6EDBD][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#D6EDBD][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#D6EDBD][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td=bgcolor:#D6EDBD][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td=bgcolor:#D6EDBD][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td=bgcolor:#D6EDBD][/td][td=bgcolor:#D6EDBD] [/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Last edited:
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I like helper columns.
You can always hide columns A and B.


Excel 2010
ABCDEFGHIJKL
1Unique CusstomersTotalsRankingTypeDateNumNameMemoAccountSplitAmountAccount Type
2CUSTOMER A30000CUSTOMER CInvoice1/4/2019263393CUSTOMER ASALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE10,000.00Income
3CUSTOMER B10000CUSTOMER AInvoice1/3/2019262826CUSTOMER ASALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE2,500.00Cost of Goods
4CUSTOMER C175000CUSTOMER BInvoice1/2/2019261925CUSTOMER ASALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE20,000.00Income
50Invoice1/2/2019263180CUSTOMER BSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE5,000.00Income
60Invoice1/2/2019263017CUSTOMER BSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE50,000.00Cost of Goods
70Invoice1/2/2019263017CUSTOMER BSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE5,000.00Income
80Invoice1/2/2019262963CUSTOMER CSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE75,000.00Income
90Invoice1/2/2019263331CUSTOMER CSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE100,000.00Income
100Invoice1/1/2019259790CUSTOMER CSALES40005 - CHTR SALES11000 - ACCOUNTS RECEIVABLE12,000.00Cost of Goods
Sheet10
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
conny2254 said:
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!
Click to expand...

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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
conny2254 said:
The solutions provided will meet my needs but I am curious, is this calculation possible without the helper column?
Click to expand...

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:


[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#0070C0]Type[/td][td=bgcolor:#0070C0]Date[/td][td=bgcolor:#0070C0]Num[/td][td=bgcolor:#0070C0]name[/td][td=bgcolor:#0070C0]Memo[/td][td=bgcolor:#0070C0]Account[/td][td=bgcolor:#0070C0]Split[/td][td=bgcolor:#0070C0]revenu[/td][td=bgcolor:#0070C0]account_type[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Invoice[/td][td]
1-4-2019​
[/td][td]
263393​
[/td][td]Buchanan, Steven[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 10.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Invoice[/td][td]
1-3-2019​
[/td][td]
262826​
[/td][td]Suyama, Michael[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
261925​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 20.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263180​
[/td][td]Leverling, Janet[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263017​
[/td][td]Peacock, Margaret[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263017​
[/td][td]Leverling, Janet[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
262963​
[/td][td]Buchanan, Steven[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 75.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263331​
[/td][td]Dodsworth, Anne[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 100.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Invoice[/td][td]
1-1-2019​
[/td][td]
269790​
[/td][td]Leverling, Janet[/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]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Invoice[/td][td]
1-4-2019​
[/td][td]
263367​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 10.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]Invoice[/td][td]
1-3-2019​
[/td][td]
262983​
[/td][td]Davolio, Nancy[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
267853​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 20.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
298456​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263012​
[/td][td]Peacock, Margaret[/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=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263915​
[/td][td]Callahan, Laura[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
267654​
[/td][td]Dodsworth, Anne[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 75.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
268961​
[/td][td]Suyama, Michael[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 100.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]Invoice[/td][td]
1-1-2019​
[/td][td]
259789​
[/td][td]Fuller, Andrew[/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]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
263915​
[/td][td]Leverling, Janet[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 5.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
267654​
[/td][td]Peacock, Margaret[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 75.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]Invoice[/td][td]
1-2-2019​
[/td][td]
268961​
[/td][td]Callahan, Laura[/td][td]SALES[/td][td]40005 - CHTR SALES[/td][td]11000 - ACCOUNTS RECEIVABLE[/td][td]
€ 100.000,00​
[/td][td]Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]Invoice[/td][td]
1-1-2019​
[/td][td]
259789​
[/td][td]Buchanan, Steven[/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]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#D6EDBD]Dodsworth, Anne[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#D6EDBD]Peacock, Margaret[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#D6EDBD]Callahan, Laura[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#D6EDBD]Suyama, Michael[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#D6EDBD]Buchanan, Steven[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#D6EDBD]Leverling, Janet[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#D6EDBD][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#D6EDBD][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#D6EDBD][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#D6EDBD][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td=bgcolor:#D6EDBD][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#D6EDBD][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]


[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#FFFF00]
6​
[/td][td][/td][td=bgcolor:#0070C0]largest total[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#D6EDBD]
15000​
[/td][td][/td][td=bgcolor:#D6EDBD]
€ 175.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#D6EDBD]
6​
[/td][td][/td][td=bgcolor:#D6EDBD]
€ 130.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD]
€ 105.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD]
€ 100.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD]
€ 85.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD]
€ 15.000,00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td][/td][td=bgcolor:#D6EDBD] [/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]
 
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
1K
Fluff
Fluff
R
  • Question Question
Excel Formula for showing a picture.
Replies
1
Views
455
Anfinsen
A
P
  • Question Question
Dependencies & Connections
Replies
0
Views
162
Pestomania
P
J
  • Locked
  • Question Question
Excel Formula
Replies
1
Views
451
Joe4
Joe4
F
  • Solved
Formula to show in 4 columns the letters from one column with condition
Replies
6
Views
300
Fractalis
F

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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