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

 

[rhogsett]

The Past Due Amount is a total of another worksheet's multiple aging buckets, ie. C2 in first post is a result of "=SUM(AgingWorksheet!G8:O8)"

 

[Aladin Akyurek]

The Past Due Amount is a total of another worksheet's multiple aging buckets, ie. C2 in first post is a result of "=SUM(AgingWorksheet!G8:O8)"

Thanks...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Account #​

[/TD]
[TD]

Customer Name​

[/TD]
[TD]

Past Due Amount​

[/TD]
[TD][/TD]
[TD]

Top N​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1A​

[/TD]
[TD]

Alpha​

[/TD]
[TD]

1,000​

[/TD]
[TD][/TD]
[TD]

Top N Adjusted​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2B​

[/TD]
[TD]

Bravo​

[/TD]
[TD]

15,000​

[/TD]
[TD][/TD]
[TD]

Top Debt Accounts​

[/TD]
[TD]

Customer​

[/TD]
[TD]

Top Due Amounts​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

3C​

[/TD]
[TD]

Charlie​

[/TD]
[TD]

20,000​

[/TD]
[TD][/TD]
[TD]7G[/TD]
[TD]Golf[/TD]
[TD]

17,000​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

4D​

[/TD]
[TD]

Delta​

[/TD]
[TD]

5,000​

[/TD]
[TD][/TD]
[TD]6F[/TD]
[TD]Foxtrot[/TD]
[TD]

15,000​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

5E​

[/TD]
[TD]

Echo​

[/TD]
[TD]

2,500​

[/TD]
[TD][/TD]
[TD]4D[/TD]
[TD]Delta[/TD]
[TD]

5,000​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

8H​

[/TD]
[TD]

QAD​

[/TD]
[TD]

1,000​

[/TD]
[TD][/TD]
[TD]5E[/TD]
[TD]Echo[/TD]
[TD]

2,500​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

6F​

[/TD]
[TD]

Foxtrot​

[/TD]
[TD]

15,000​

[/TD]
[TD][/TD]
[TD]1A[/TD]
[TD]Alpha[/TD]
[TD]

1,000​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

7G​

[/TD]
[TD]

Golf​

[/TD]
[TD]

17,000​

[/TD]
[TD][/TD]
[TD]8H[/TD]
[TD]QAD[/TD]
[TD]

1,000​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

Excluded Account #​

[/TD]
[TD]

Excluded Customer Name​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

2B​

[/TD]
[TD]

Bravo​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

3C​

[/TD]
[TD]

Charlie​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


F1: 5 (Top N, adjust as desired.)

In F2 Control+shift+enter, not just enter:

=SUM(IF(ISNA(MATCH(A2:A9,A13:A14,0)),
    IF(C2:C9>=LARGE(IF(ISNA(MATCH(A2:A9,A13:A14,0)),$C$2:$C$9),
    MIN(F1,SUM(IF(ISNA(MATCH(A2:A9,A13:A14,0)),1)))),1)))

In E4 control+shift+enter and copy down:

=IF($G4="","",INDEX($A$2:$A$9,SMALL(IF(ISNA(MATCH($A$2:$A$9,$A$13:$A$14,0)),
       IF($C$2:$C$9=$G4,ROW($A$2:$A$9)-ROW($A$2)+1)),COUNTIFS($G$4:G4,G4))))

In F4 just enter and copy down:

=IF($E4="","",VLOOKUP($E4,$A$2:$B$9,2,0))

In G4 control+shift+enter and copy down:

=IF(ROWS($G$4:G4)<=$F$2,LARGE(IF(ISNA(MATCH($A$2:$A$9,$A$13:$A$14,0)),
    $C$2:$C$9),ROWS($G$4:G4)),"")

 

[Eric W]

I'd hoped that DRSteele would respond, since it was his formula. However, since he's away for a bit, I'll give it a shot. I modified his original formula a bit by adding the row number/1000 to each value internally. This insures that there will be no ties, and has the effect that if there is a tie, the account lower down will get the lower numbered rating.

	A	B	C	D	K	L	M	N	O	P	Q
Top 5 Highest Past Due Accounts:
9I	Indigo
	7G	Golf
	6F	Foxtrot
4D	Delta
11K	Kilo
Select Ranking
5E	Echo
Select Name	Ranking
Jackson	10J	Jackson

<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: center"]Ranking[/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: 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"]9[/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]Bravo[/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]3C[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17000[/TD]
[TD="align: right"]2[/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"]15000[/TD]
[TD="align: right"]3[/TD]

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

[TD="align: right"]15000[/TD]
[TD="align: right"]4[/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"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]14123[/TD]
[TD="align: right"]5[/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"]3[/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"]2[/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: center"]8H[/TD]
[TD="align: center"]Hula[/TD]
[TD="align: right"]4,444[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

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

[TD="align: center"]10[/TD]
[TD="align: center"]9I[/TD]
[TD="align: center"]Indigo[/TD]
[TD="align: right"]17,500[/TD]
[TD="align: right"]1[/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"]11[/TD]
[TD="align: center"]10J[/TD]
[TD="align: center"]Jackson[/TD]
[TD="align: right"]5,555[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]12[/TD]
[TD="align: center"]11K[/TD]
[TD="align: center"]Kilo[/TD]
[TD="align: right"]14,123[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

</tbody>

Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]=INDEX(A$2:A$12,MATCH(ROWS($N$2:$N2),$D$2:$D$12,0))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N9[/TH]
[TD="align: left"]=INDEX(A$2:A$12,MATCH($M$9,$D$2:$D$12,0))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N12[/TH]
[TD="align: left"]=INDEX(A$2:A$12,MATCH($M$12,$B$2:$B$12,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]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(ISNA(MATCH(A2,$K$2:$K$3,0)),SUMPRODUCT(--(C2+ROW(C2)/1000<$C$2:$C$12+ROW($C$2:$C$12)/1000),--ISNA(MATCH($A$2:$A$12,$K$2:$K$3,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]



Put the D2 formula in and confirm with Control+Shift+Enter. Copy it and paste it down the column.

The rest of the formulas are now just lookups. Put the N2 formula in and paste it to N2:Q6. Put the N9 formula in and paste to O9:Q9. Put the N12 formula in and paste to O12:Q12.

Now the top 5 accounts will automatically populate N2:Q6. You can select the ranking you want to see by putting a number in M9. You can select the name you want to see by putting it in M12.

Hope this helps.

 

[DRSteele]

EricW I think I've lost the plot here. I thought what I'd originally proposed and then amended to account for tied figures was working on my computer. Then came all kinds of new suggestions and I didn't want to add more noise.

 

[rhogsett]

Eric,

Thank you. I keep receiving the #Value with the D2 formula. Any thought what may be causing this?

 

[Eric W]

Best guess is that you have some non-numeric data in your amount range. Empty cells are OK, cells with spaces are not. You can try to find those and clean them up, or you can use this array function:

=IF(ISNA(MATCH(A2,$K$2:$K$3,0)),SUMPRODUCT(--(IFERROR(C2+ROW(C2)/1000,0)< IFERROR($C$2:$C$12+ROW($C$2:$C$12)/1000,0)),--ISNA(MATCH($A$2:$A$12,$K$2:$K$3,0)))+1,"")

which ignores non-numeric data.

 

[rhogsett]

Eric,

Thank you. I keep receiving the #Value with the D2 formula. Any thought what may be causing this?

I may have figured out the issue. In the formula, since the number of accounts on the Aging Report fluctuate, I extended the range for Account # (Column A) and Amount Past Due (Column C) beyond $C$12. This appears to be causing the "#Value" error.

This does not help solve the issue, but at least (I think) the issue is identified.

 

[rhogsett]

Best guess is that you have some non-numeric data in your amount range. Empty cells are OK, cells with spaces are not. You can try to find those and clean them up, or you can use this array function:

=IF(ISNA(MATCH(A2,$K$2:$K$3,0)),SUMPRODUCT(--(IFERROR(C2+ROW(C2)/1000,0)< IFERROR($C$2:$C$12+ROW($C$2:$C$12)/1000,0)),--ISNA(MATCH($A$2:$A$12,$K$2:$K$3,0)))+1,"")

which ignores non-numeric data.

This worked perfectly, thank you!