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:[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Account #[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Past Due Amount[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1A[/TD]
[TD="align: center"]Alpha[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]Bravo[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3C[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: center"]20,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4D[/TD]
[TD="align: center"]Delta[/TD]
[TD="align: center"]5,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]5E[/TD]
[TD="align: center"]Echo[/TD]
[TD="align: center"]2,500[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]6F[/TD]
[TD="align: center"]Foxtrot[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]7G[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]17,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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

Do Not Include:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Excluded Account #[/TD]
[TD="align: center"]Excluded Customer Name[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]Bravo[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]3C[/TD]
[TD="align: center"]Charlie[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


...in order to produce the results below:

Top 5 Highest Past Due Accounts:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]Account #[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Past Due Amount[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]7G[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]17,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"]6F[/TD]
[TD="align: center"]Foxtrot[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]4D[/TD]
[TD="align: center"]Delta[/TD]
[TD="align: center"]5,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]5E[/TD]
[TD="align: center"]Echo[/TD]
[TD="align: center"]2,500[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]1A[/TD]
[TD="align: center"]Alpha[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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
Top 5 Highest Past Due Accounts:
7G	Golf
6F	Foxtrot
4D	Delta
5E	Echo
1A	Alpha

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Account #[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Past Due Amount[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Excluded Account #[/TD]
[TD="align: center"]Excluded Customer Name[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1A[/TD]
[TD="align: center"]Alpha[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]Bravo[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17000[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]Bravo[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3C[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15000[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3C[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: center"]20,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5000[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4D[/TD]
[TD="align: center"]Delta[/TD]
[TD="align: center"]5,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2500[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5E[/TD]
[TD="align: center"]Echo[/TD]
[TD="align: center"]2,500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1000[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6F[/TD]
[TD="align: center"]Foxtrot[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7G[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]17,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O2[/TH]
[TD="align: left"]=INDEX(B:B,MATCH($N2,$A:$A,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]{=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))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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
1A	Alpha
2B	Bravo
3C	Charlie
4D	Delta
5E	Echo
6F	Foxtrot
7G	Golf
2B	Bravo
3C	Charlie

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Account #[/TD]
[TD="bgcolor: #FCE4D6"]Customer Name[/TD]
[TD="bgcolor: #FCE4D6"]Past Due Amount[/TD]
[TD="bgcolor: #FCE4D6"]Rank (Excluded)[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]15,000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]20,000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2,500[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]15,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFF2CC"]Excluded Account #[/TD]
[TD="bgcolor: #FFF2CC"]Excluded Customer Name[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFE699"]Account #[/TD]
[TD="bgcolor: #FFE699"]Customer Name[/TD]
[TD="bgcolor: #FFE699"]Past Due Amount[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #E2EFDA"]7G[/TD]
[TD="bgcolor: #E2EFDA"]Golf[/TD]
[TD="bgcolor: #E2EFDA, align: right"]17000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #E2EFDA"]6F[/TD]
[TD="bgcolor: #E2EFDA"]Foxtrot[/TD]
[TD="bgcolor: #E2EFDA, align: right"]15000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #E2EFDA"]4D[/TD]
[TD="bgcolor: #E2EFDA"]Delta[/TD]
[TD="bgcolor: #E2EFDA, align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #E2EFDA"]5E[/TD]
[TD="bgcolor: #E2EFDA"]Echo[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #E2EFDA"]1A[/TD]
[TD="bgcolor: #E2EFDA"]Alpha[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A21[/TH]
[TD="align: left"]=IFNA(INDEX(A$2:A$8,MATCH(ROWS(A$2:A2),$D$2:$D$8,0)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=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,"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Top Highest Past Due Accounts:

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]
[TD="bgcolor: #DBDBDB"]Top[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #DBDBDB"]Hurdle[/TD]
[TD="bgcolor: #CCFFCC, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #DBDBDB"]Count[/TD]
[TD="bgcolor: #CCFFCC, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFE699"]No[/TD]
[TD="bgcolor: #FFE699"]Rank[/TD]
[TD="bgcolor: #FFE699"]Account #[/TD]
[TD="bgcolor: #FFE699"]Customer Name[/TD]
[TD="bgcolor: #FFE699"]Past Due Amount[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1[/TD]
[TD="bgcolor: #C6E0B4"]7G[/TD]
[TD="bgcolor: #C6E0B4"]Golf[/TD]
[TD="bgcolor: #C6E0B4, align: right"]17000[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2[/TD]
[TD="bgcolor: #C6E0B4"]6F[/TD]
[TD="bgcolor: #C6E0B4"]Foxtrot[/TD]
[TD="bgcolor: #C6E0B4, align: right"]15000[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3[/TD]
[TD="bgcolor: #C6E0B4"]4D[/TD]
[TD="bgcolor: #C6E0B4"]Delta[/TD]
[TD="bgcolor: #C6E0B4, align: right"]5000[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #C6E0B4, align: right"]4[/TD]
[TD="bgcolor: #C6E0B4, align: right"]4[/TD]
[TD="bgcolor: #C6E0B4"]5E[/TD]
[TD="bgcolor: #C6E0B4"]Echo[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2500[/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #C6E0B4, align: right"]5[/TD]
[TD="bgcolor: #C6E0B4, align: right"]5[/TD]
[TD="bgcolor: #C6E0B4"]1A[/TD]
[TD="bgcolor: #C6E0B4"]Alpha[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1000[/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"][/TD]

</tbody>

rankif​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]=SMALL(D2:D8,B14)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B16[/TH]
[TD="align: left"]=COUNTIF(D2:D8,"<="&B15)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A21[/TH]
[TD="align: left"]=IF(ROWS(D$2:D2)>$B$16,"",ROWS(D$2:D2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B21[/TH]
[TD="align: left"]=IF(A21="","",SMALL($D$2:$D$8,A21))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C21[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D21[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E21[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Account #[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Past Due Amount[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7G[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]17000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6F[/TD]
[TD="align: center"]Foxtrot[/TD]
[TD="align: center"]15000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4D[/TD]
[TD="align: center"]Delta[/TD]
[TD="align: center"]5000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5E[/TD]
[TD="align: center"]Echo[/TD]
[TD="align: center"]2500[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1A[/TD]
[TD="align: center"]Alpha[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]##[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]##[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]##[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table"]
<tbody></tbody>[/TABLE]
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?