Can I use LARGE and COUNTIFS to exclude values in a range?

[rhogsett]

I have a list of customer accounts with various past due balances. I would like a formula to rank the accounts based on past due values while excluding specific accounts that have been placed on a separate "Do Not Include" list.

I have used LARGE, which works great until I need to exclude certain accounts from the Top 5 List. I am having difficulty finding/constructing a formula that can achieve the exampled Top 5 Highest Past Due Accounts below.

Please see the sample report below...

Aging Report:

	A	B	C
1	Account #	Customer Name	Past Due Amount
2	1A	Alpha	1,000
3	2B	Bravo	15,000
4	3C	Charlie	20,000
5	4D	Delta	5,000
6	5E	Echo	2,500
7	6F	Foxtrot	15,000
8	7G	Golf	17,000

<tbody>
</tbody>

...that needs to evaluate the exclusion list below...

Do Not Include:

	A	B
	Excluded Account #	Excluded Customer Name
10	2B	Bravo
11	3C	Charlie

<tbody>
</tbody>

...in order to produce the results below:

Top 5 Highest Past Due Accounts:

	A	B	C
20	Account #	Customer Name	Past Due Amount
21	7G	Golf	17,000
22	6F	Foxtrot	15,000
23	4D	Delta	5,000
24	5E	Echo	2,500
25	1A	Alpha	1,000

<tbody>
</tbody>

Any assistance is greatly appreciated! Please let me know where I can provide further clarification.

Thank you,

RGH

 

[Eric W]

Here's one possibility:

	A	B	C	D	K	L	M	N	O	P
1	Account #	Customer Name	Past Due Amount		Excluded Account #	Excluded Customer Name		Top 5 Highest Past Due Accounts:
2	1A	Alpha	1,000		2B	Bravo		7G	Golf	17000
3	2B	Bravo	15,000		3C	Charlie		6F	Foxtrot	15000
4	3C	Charlie	20,000					4D	Delta	5000
5	4D	Delta	5,000					5E	Echo	2500
6	5E	Echo	2,500					1A	Alpha	1000
7	6F	Foxtrot	15,000
8	7G	Golf	17,000
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell Formula O2 =INDEX(B:B,MATCH($N2,$A:$A,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula N2 {=INDEX(A:A,MIN(IF(($C$2:$C$8=MAX(IF(COUNTIF($K$2:$K$3,$A$2:$A$8)+COUNTIF($N$1:$N1,$A$2:$A$8)=0,$C$2:$C$8)))*(COUNTIF($K$2:$K$3,$A$2:$A$8)+COUNTIF($N$1:$N1,$A$2:$A$8)=0),ROW($C$2:$C$8))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You can set up your sheet in a lot of ways, just change the ranges to match your sheet. The big formula goes in N2, and N1 is required to have something in it (a title in this example). Confirm the array formula in N2 with Control+Shift+Enter. The formula in O2 is just a simple INDEX based on N2. Put that in and copy it to P2 and down the columns.

That's an awfully awkward formula. It may be possible to simplify it, but I can't see what to cut at the moment. I'll let you know if I come up with something better. Let me know if you have questions.

 

[DRSteele]

EricW always beats me to it with a better solution! But I worked on this one and so I include it. It has a helper column to show the 'rankif' algorithm. Copy down and across to populate the green cells with the two formulas.

	A	B	C	D
1	Account #	Customer Name	Past Due Amount	Rank (Excluded)
2	1A	Alpha	1,000	5
3	2B	Bravo	15,000
4	3C	Charlie	20,000
5	4D	Delta	5,000	3
6	5E	Echo	2,500	4
7	6F	Foxtrot	15,000	2
8	7G	Golf	17,000	1
9	Excluded Account #	Excluded Customer Name
10	2B	Bravo
11	3C	Charlie
12
13
14
15
16
17
18
19
20	Account #	Customer Name	Past Due Amount
21	7G	Golf	17000
22	6F	Foxtrot	15000
23	4D	Delta	5000
24	5E	Echo	2500
25	1A	Alpha	1000
26
27

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula A21 =IFNA(INDEX(A$2:A$8,MATCH(ROWS(A$2:A2),$D$2:$D$8,0)),"") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=IF(ISNA(MATCH(A2,$A$10:$A$11,0)),SUMPRODUCT(--(C2<$C$2:$C$8),--ISNA(MATCH($A$2:$A$8,$A$10:$A$11,0)))+1,"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Eric W]

I'm not so sure mine is better! I don't use a helper column, but your formula is significantly shorter, and probably more efficient. It's always nice to see different approaches to solve the same problem, especially for these tricky ones. I wonder if these can be combined somehow. Ain't synergy wonderful?

 

[DRSteele]

You're such a diplomat! Synergy rocks. But mine is inferior because it chokes on ties.

I'm trying to remedy the situation by watching this video and applying my newfound knowledge. https://www.youtube.com/watch?v=rKDI-kdBsjY

 

[DRSteele]

I think I successfully altered this so ties are accounted for properly.

	A	B	C	D	E
14	Top	5
15	Hurdle	5
16	Count	5
17
18
19	Top Highest Past Due Accounts:
20	No	Rank	Account #	Customer Name	Past Due Amount
21	1	1	7G	Golf	17000
22	2	2	6F	Foxtrot	15000
23	3	3	4D	Delta	5000
24	4	4	5E	Echo	2500
25	5	5	1A	Alpha	1000
26
27

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

rankif​

Worksheet Formulas Cell Formula B15 =SMALL(D2:D8,B14) B16 =COUNTIF(D2:D8,"<="&B15) A21 =IF(ROWS(D$2:D2)>$B$16,"",ROWS(D$2:D2)) B21 =IF(A21="","",SMALL($D$2:$D$8,A21)) C21 =IF($A21="","",INDEX(A$2:A$8,AGGREGATE(15,6,(ROW($D$2:$D$8)-ROW($D$2)+1)/($D$2:$D$8=$B21),COUNTIF($B$21:$B21,$B21)))) D21 =IF($A21="","",INDEX(B$2:B$8,AGGREGATE(15,6,(ROW($D$2:$D$8)-ROW($D$2)+1)/($D$2:$D$8=$B21),COUNTIF($B$21:$B21,$B21)))) E21 =IF($A21="","",INDEX(C$2:C$8,AGGREGATE(15,6,(ROW($D$2:$D$8)-ROW($D$2)+1)/($D$2:$D$8=$B21),COUNTIF($B$21:$B21,$B21)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[rhogsett]

Eric, thank you so much! This works great, but I have a much more basic question. How do I enable to formula to work by referencing a specific Rank Number (A1:A9). I like to keep a couple of Cells (A7:A9), for example, blank so I can search for any rank of my customer list and have it complete respective cells in columns B through D?

	A	B	C	D	E
1	Rank	Account #	Customer Name	Past Due Amount
2	1	7G	Golf	17000
3	2	6F	Foxtrot	15000
4	3	4D	Delta	5000
5	4	5E	Echo	2500
6	5	1A	Alpha	1000
7	##
8	##
9	##

<tbody>
</tbody>

<tbody></tbody>

Any thoughts?

*A side note, I would like to eventually be able to search for the customer name in a cell and have it produce Rank and Past Due Amount, but this is a great start!

 

[Eric W]

So you want to put a 9 in A7, and get the 9th ranking account? I think you might have hit the limits of what my formula can do. My formula excludes account numbers above it, and on the exclude list, finds the maximum amount of the remaining accounts, then matches that amount to get the matching account. It won't work without a list of all the ranks above it.

However, I think DRSteele's formula might work for you. If you add the rank column in D, like he shows in post #3, then the ranking for all accounts will be listed. Then if you want to show the 9th ranking account, it would be a simple matter to do an INDEX/MATCH on the number 9 in column D. (Ties might require a little work.) It would also work if you want to enter a name, just do an INDEX/MATCH on the name (or even use a Data Validation drop-down box), and pull the matching information from A, C, and D.

I can't think of any way to do this without a helper column, short of using VBA.

 

[rhogsett]

Eric,

Thank you for your response. I am reviewing DRSteele's D2 formula from the 3rd post but cannot get it to work for me. The formula keeps returning a BLANK/"" result, not sure what I'm missing here.

 

[Aladin Akyurek]

Does the data consist of just a single record of past due amount per customer, that is, are these already aggregated per customer?