Counting using frequency formula with multiple criteria

Beefboy

New Member
Joined
Jul 10, 2017
Messages
2
I have a financial modeling issue I can not solve.

I am seeking:

1. The number of unique obligors with a senior debt principle value investments >0
2. The number of unique obligors with a principle value investment >0


[TABLE="width: 945"]
<tbody>[TR]
[TD="align: center"]A,1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]What I expect the results to be:[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Obligor ID[/TD]
[TD="align: center"]Principle Value[/TD]
[TD="align: center"]Senior Debt
(Yes = 1)[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Unique Obligors with Senior Debt Investments w/ Principle > 0[/TD]
[TD="align: center"]Unique Obligors w/ Principle > 0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]300[/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"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]115[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/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"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Result:[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody><colgroup><col style="text-align: center;" span="2"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup>[/TABLE]



I have been trying to utilize a derivative of the formula provided in the following thread but cant get it right.

=SUM(IF(FREQUENCY(IF(A6:A100=E2,IF(C6:C100<>"",C6:C100)),IF(A6:A100=E2,IF(C6:C100<>"",C6:C100))),1))
https://www.mrexcel.com/forum/excel...sing-frequency-formula-multiple-criteria.html

Your help would be greatly appreciated,

Chris
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
If your ID's are numeric then formulas in E & F17.
If ID's are text then formulas in E & F18.
These are array formulas and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEF
1Obligor IDPrinciple ValueSenior DebtUnique Obligors with Senior Debt Investments w/ Principle > 0Unique Obligors w/ Principle > 0
2(Yes = 1)
311001
422001
52300
63110
74115
851201
951601
1062001
117300
12801
1381001
14901
1590
16Result:
1758
1858
Sheet
 
Upvote 0
You're welcome. Thanks for the feedback and welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

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