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

 

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

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Account #​	Customer Name​	Past Due Amount​		Top N​	5​
2​	1A​	Alpha​	1,000​		Top N Adjusted​	6​
3​	2B​	Bravo​	15,000​		Top Debt Accounts​	Customer​	Top Due Amounts​
4​	3C​	Charlie​	20,000​		7G	Golf	17,000​
5​	4D​	Delta​	5,000​		6F	Foxtrot	15,000​
6​	5E​	Echo​	2,500​		4D	Delta	5,000​
7​	8H​	QAD​	1,000​		5E	Echo	2,500​
8​	6F​	Foxtrot​	15,000​		1A	Alpha	1,000​
9​	7G​	Golf​	17,000​		8H	QAD	1,000​
10​
11​
12​	Excluded Account #​	Excluded Customer Name​
13​	2B​	Bravo​
14​	3C​	Charlie​

<tbody>
</tbody>

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
1	Account #	Customer Name	Past Due Amount	Ranking	Excluded Account #	Excluded Customer Name		Top 5 Highest Past Due Accounts:
2	1A	Alpha	1,000	9	2B	Bravo		9I	Indigo	17500	1
3	2B	Bravo	15,000		3C	Charlie		7G	Golf	17000	2
4	3C	Charlie	20,000					6F	Foxtrot	15000	3
5	4D	Delta	15,000	4				4D	Delta	15000	4
6	5E	Echo	2,500	8				11K	Kilo	14123	5
7	6F	Foxtrot	15,000	3
8	7G	Golf	17,000	2			Select Ranking
9	8H	Hula	4,444	7			8	5E	Echo	2500	8
10	9I	Indigo	17,500	1
11	10J	Jackson	5,555	6			Select Name				Ranking
12	11K	Kilo	14,123	5			Jackson	10J	Jackson	5555	6

<tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell Formula N2 =INDEX(A$2:A$12,MATCH(ROWS($N$2:$N2),$D$2:$D$12,0)) N9 =INDEX(A$2:A$12,MATCH($M$9,$D$2:$D$12,0)) N12 =INDEX(A$2:A$12,MATCH($M$12,$B$2:$B$12,0)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=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,"")} <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>

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!